plpgsql - stored procedure Comma separated values in parameters

Question

Im writing a stored procedure to get database and table names in variables and then I'll push these values into IN clause.

CREATE OR REPLACE PROCEDURE bhuvi(db varchar(100), tbls varchar(100))
 LANGUAGE plpgsql
 AS $$
 DECLARE
   rec RECORD;
    v timestamp;
   tables_s varchar(100);

 BEGIN
 select '''' + REPLACE(tbls, ',', ''',''') + '''' into tables_s;
    FOR rec IN select table_schema, table_name from information_schema.tables where table_catalog in (db) and table_type='BASE TABLE' and table_schema not in ('pg_catalog','information_schema')  and table_name in (tables_s)
   LOOP
   select now() into v;
     RAISE INFO ' % printing... schema = % and table = %',v, rec.table_schema, rec.table_name;
 END LOOP;
 END;
 $$;    

If I call this procedure, its just showing CALL. No results.

But in my FOR LOOP, if I remove the and table_name in (tables_s), Im able to print all table names and schemas.

But something wrong with the multiple table names in the variable. Without that its working.

Can someone help me to figure it out what was wrong in my script?

Answer 1

The best solution is to not pass a single varchar parameter but an array:

CREATE OR REPLACE PROCEDURE bhuvi(p_catalogs text[], p_tbls text[])
 LANGUAGE plpgsql
 AS $$
 DECLARE
   rec RECORD;
    v timestamp;
 BEGIN
    FOR rec IN select table_schema, table_name 
               from information_schema.tables 
               where table_catalog = any (p_catalogs) 
               and table_type='BASE TABLE' 
               and table_schema not in ('pg_catalog','information_schema')  
               and table_name = any (p_tbls)
   LOOP
     select now() into v;
     RAISE INFO ' % printing... schema = % and table = %',v, rec.table_schema, rec.table_name;
 END LOOP;
 END;
$$;    

Then you can call it e.g. like this:

call bhuvi(array['schema_1',schema_2'], array['table_1', 'table_2']);

---

If you can't change the calling code to deal with array, convert the comma separated strings to arrays

CREATE OR REPLACE PROCEDURE bhuvi(p_catalogs text, p_tbls text)
 LANGUAGE plpgsql
 AS $$
 DECLARE
   rec RECORD;
    v timestamp;
 BEGIN
    FOR rec IN select table_schema, table_name 
               from information_schema.tables 
               where table_catalog = any (string_to_array(p_catalogs.',')) 
               and table_type='BASE TABLE' 
               and table_schema not in ('pg_catalog','information_schema')  
               and table_name = any (string_to_array(p_tbls, ','))
   LOOP
     select now() into v;
     RAISE INFO ' % printing... schema = % and table = %',v, rec.table_schema, rec.table_name;
 END LOOP;
 END;
$$;