主页 / dba / 问题 / 250323
Accepted
TheDataGuy
Asked: 2019-10-06 04:47:20 +0800 CST

plpgsql - 存储过程参数中的逗号分隔值

  • 772

我正在编写一个存储过程来获取变量中的数据库和表名，然后我将把这些值推送到 IN 子句中。 

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

如果我调用此过程，它只会显示 CALL。没有结果。

但是在我的 FOR 循环中，如果我删除and table_name in (tables_s)，我能够打印所有表名和模式。

但是变量中的多个表名有问题。没有它的工作。

有人可以帮我弄清楚我的脚本有什么问题吗？

postgresql plpgsql

1 个回答

  1. Best Answer

    最好的解决方案是不传递单个varchar参数，而是传递一个数组：

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

    然后你可以这样称呼它：

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

    如果您不能更改调用代码来处理数组，请将逗号分隔的字符串转换为数组

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

相关问题