Validate array of table names

Question

I have requirement where bash script will pass on the string consisting tables names to anonymous PL/pgSQL block which will process the tables if they exists in the Postgres db.

Below is the PL/pgSQL code assuming given_tables variable has the string consisting table names provided through bash script.

do $do$
<<outerblock>>
declare
    tab text;
    given_tables text[] := ARRAY['ocab1.cust_docu_map','ocsbc2.cust_docu_map'];
    table_names  text[] := ARRAY(
                              BEGIN
                                FOREACH tab IN ARRAY given_tables loop
                                    IF EXISTS
                                        ( SELECT 1
                                          FROM pg_class
                                          WHERE oid = tab::regclass
                                        )
                                    THEN
                                          SELECT concat_ws('.',relnamespace::regnamespace::text,relname)
                                          FROM pg_class
                                          WHERE oid = tab::regclass
                                          AND relreplident != 'f';
                                    END IF;
                                END LOOP;
                              END
                            );
begin
  foreach table in array table_names loop
    raise notice 'here is the table - %',outerblock.table;  
  end loop;
end $do$;

But code errors out:

ERROR:  mismatched parentheses at or near ";"
LINE 18:             AND relreplident != 'f';
                                            ^

Not sure where I am going wrong?

Answer 1

DO
$do$
DECLARE
   _given_tbls text[] := '{ocab1.cust_docu_map,ocsbc2.cust_docu_map}';
   _tbl text;
BEGIN
   FOR _tbl IN
      SELECT format('%I.%I', n.nspname, c.relname)         -- properly quoted
      FROM   unnest(_given_tbls) t(tbl)
      JOIN   pg_class     c ON c.oid = to_regclass(t.tbl)  -- does not raise exception
      JOIN   pg_namespace n ON n.oid = c.relnamespace
      WHERE  c.relreplident != 'f'
   LOOP
      RAISE NOTICE 'valid table: %', _tbl;  
      -- or do something here!
   END LOOP;
END
$do$;

to_regclass(_tab) does not raise an exception for invalid table names. See:

• How to check if a table exists in a given schema

Remember that identifiers stored in catalog tables may need double-quoting. Quote with format() or quote_ident() to defend against syntax errors or even SQL injection attacks.
regclass values are quoted automatically when converted to text, but only schema-qualified when necessary with the current search_path. You seem to want fully qualified names. See:

• Table name as a PostgreSQL function parameter

Also, it's much cheaper to run a single query and loop through results (if you really need to loop at all?), than to run another query (or even two!) for every element in the array.