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

Question

我正在尝试编写一个函数来从Redshift获取架构中的对象列表。我创建了一个从RDS PostgreSQL到Redshift的数据库链接。查询在单独调用时工作正常，但在带有参数的函数中编写时不起作用。我想传递多个参数(模式名称(，因此我使用了可变参数。函数如下所示 -

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('{公共，pg_catalog}'(

   错误：没有参数 $1 其中：名为"unnamed"的 dblink 连接上发生错误：无法 执行查询。

2. 从f_fetch_tables中选择 *(可变参数 '{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有关 - PLpgSQLEXECUTE命令执行的查询具有自己的参数环境。因此，您不能使用函数环境中的变量或参数引用。

此代码不起作用：

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子句。但是您仅在命令级别使用 USING 子句EXECUTE- 而不是在不支持dblink级别上使用 USING 子句。dblinkAPI 与EXECUTE命令的USING子句没有什么类似的内容。因此，在将它发送到 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