Postgresql函数-使用文本类型的VARIADIC参数

Question

我正在尝试编写一个函数，以便从Redshift获取模式中的对象列表。我已经创建了一个从RDS PostgreSQL到Redshift的dblink。当单独调用时，查询工作得很好，但在带有参数的函数中编写时则不起作用。我想传递多个参数(模式名称)，因此我使用了VARIADIC参数。功能如下所示-

CREATE FUNCTION f_fetch_tables(VARIADIC list text[]) 
RETURNS VOID
AS $$
    DECLARE
        begin_time TIMESTAMP;
        expire_time TIMESTAMP;
    BEGIN   
        /* To fetch the list of all objects from Redshift */
        EXECUTE 'drop table if exists tmp_rs_obj_list;
        create table tmp_rs_obj_list as 
        SELECT * FROM dblink(''rs_link'',$REDSHIFT$ select * from (select schemaname, 
        tablename from pg_tables UNION select schemaname, viewname from pg_views) where schemaname 
        not in (array_to_string($1,'','')) $REDSHIFT$) AS t1 (schema_nm varchar(30), obj_nm varchar(100))' using list;
    END;
  $$
  LANGUAGE plpgsql
;

这个函数编译得很好，并且被成功地创建了，但是我想不出一种叫它的方法-

到目前为止还用过这些电话，没有任何运气-

1. 选择f_fetch_tables({public，pg_catalog}) 错误:没有参数$1，其中:错误发生在名为“unnamed”的dblink连接上:无法执行查询。
2. 从f_fetch_tables中选择*(VARIADIC '{public，pg_catalog}') 错误:没有参数$1，其中:错误发生在名为“unnamed”的dblink连接上:无法执行查询。

任何建议都会很有帮助。

谢谢你，卡姆莱什

Answer 1

你的功能有几个问题。我建议使用：

• 函数format()以便于传递参数，
• 美元引用的($fmt$)查询在execute中，
• <> all(array)而不是not in运算符(不必将数组转换为字符串)。

经建议的修改后的职能：

create or replace function f_fetch_tables(variadic list text[]) 
returns void
as $$
    declare
        begin_time timestamp;
        expire_time timestamp;
    begin   
        /* to fetch the list of all objects from redshift */
        execute format($fmt$
            drop table if exists tmp_rs_obj_list;
            create table tmp_rs_obj_list as 
                select * 
                from dblink(
                    'rs_link', $redshift$ 
                        select * 
                        from (
                            select schemaname, tablename 
                            from pg_tables 
                            union 
                            select schemaname, viewname 
                            from pg_views) s
                        where schemaname <> all(%L)
                    $redshift$) 
                    as t1 (schema_nm varchar(30), obj_nm varchar(100))
            $fmt$, list);
    end;
$$
language plpgsql;

还请注意将参数传递给具有可变参数的函数的适当方法：

select f_fetch_tables('pg_catalog', 'information_schema');
select * from tmp_rs_obj_list;

Answer 2

此问题与可变参数无关--如果您也使用正常参数，您将得到相同的行为。它与动态SQL相关-- EXECUTE命令从PLpgSQL执行的查询具有自己的参数环境。因此，您不能使用来自函数环境的变量或param引用。

此代码不起作用：

CREATE OR REPLACE FUNCTION fx(a int)
RETURNS void AS $$
BEGIN
  EXECUTE 'SELECT * FROM foo WHERE foo.a = $1';
END;
$$ LANGUAGE plpgsql;

在这种情况下，没有传递任何参数来执行查询。$1无效。当要将一些参数传递给动态SQL时，应该使用USING子句。

此代码应能工作：

CREATE OR REPLACE FUNCTION fx(a int)
RETURNS void AS $$
BEGIN
  EXECUTE 'SELECT * FROM foo WHERE foo.a = $1' USING a;
END;
$$ LANGUAGE plpgsql;

但是它也不能解决您的问题，因为您使用的是USING子句。但是，在不支持子句的情况下，只在级别EXECUTE命令上使用子句，而不是在dblink级别使用子句。dblink API没有类似于EXECUTE命令的USING子句。因此，在将本机SQL字符串发送到dblink API之前，必须使用解压缩(预评估)参数构建本机SQL字符串。

您正在使用两个级别的动态SQL。

• EXECUTE
  • dblink

​

dblink不支持查询参数化，所以您不应该在那里使用参数位置持有者。$x。

在本例中，最好将输入数组序列化为顶级plpgsql级别的字符串，并像动态SQL参数那样传递该字符串。

DECLARE serialized_params text;
BEGIN
  serialized_params = (SELECT array_agg(quote_literal(quote_ident(v))) FROM unnest(ARRAY['A','b']) g(v));
  EXECUTE ' ....' USING serialized_params;
END