0

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?

2

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;
$$;    
  • 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? – Bhuvanesh Oct 5 at 15:29
  • @Bhuvanesh: if the first version does not work on Redshift I have no idea – a_horse_with_no_name Oct 5 at 15:35
  • 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. – Bhuvanesh Oct 5 at 15:37
  • 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! – Vérace Oct 5 at 18:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.