Sql 表名数组

发布于04月04日

我有一个要求，bash脚本将字符串组成的表名传递给匿名PL/pgSQL块，如果它们存在于Postgr数据库中，该块将处理表.

下面是PL/pgSQL代码，假设given_tables变量具有由bash脚本提供的表名组成的字符串.

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

代码错误:

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

不知道我哪里错了？

推荐答案

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)不引发无效表名的异常.参见:

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: