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;
$$; 

• TheDataGuy: Both steps are working great. Now Im syncing a few tables to Redshift, there I can't use string_to_array , would you mind to suggest something alternate for this?
• user1822: @Bhuvanesh: if the first version does not work on Redshift I have no idea
• TheDataGuy: in first version, it didn't accept text [] in the variable. ERROR: varchar[] is not a supported parameter type for functions or procedures I guess its due to old version of postgresql.
• Vérace: SELECT VERSION();. See here - PostgreSQL Version: 9.3. From here - is based on an older version of PostgreSQL 8.0.2, and Redshift has made changes to that version so God alone knows what they've done to the code!