PostgreSQL传播数组值作为using子句的输入参数 [英] PostgreSQL spread array values as input parameters for using clause

问题描述

CREATE OR REPLACE FUNCTION demo(vsql text, vals text[])
  RETURNS void AS
$BODY$
BEGIN

    execute vsql using vals;

END;
$BODY$
LANGUAGE plpgsql VOLATILE;


select demo('select $1 || $2', ARRAY['Barrett', ' Gilmour'] );

ERROR:  there is no parameter $2
LINE 1: select $1 || $2

错误是postgres不能理解数组中的两个项目必须散布到输入参数$ 1和$ 2。它将整个数组理解为$ 1

The error is that postgres does not understand that the two items in the array must be spread to the input parameters $1 and $2. It understand the entire array as value for $1

推荐答案

的值，如SO问题https://dba.stackexchange.com/questions/83278/postgressql-dynamic-execute-with-argument-values -in-array ，则EXECUTE ... USING将把数组视为单个参数。

As discussed in SO question https://dba.stackexchange.com/questions/83278/postgressql-dynamic-execute-with-argument-values-in-array, the EXECUTE ... USING will treat an array as a single parameter.

您可以尝试使用此hack，它修改传递的SQL语句，使$ n变为$ 1 [n]

You could try this hack, which edits the passed SQL statement so that $n becomes $1[n]

vsql := regexp_replace( vsql, '[$]([0-9]+)', '$1[\1]', 'g' );

vals 数组被视为单个数组

a_horse_with_no_name提到了另一个问题，即您的函数实际上不返回任何内容。如果要查看正在执行的操作：

a_horse_with_no_name mentioned your other problem which is that your function doesn't actually return anything. If you want to see what is being executed:

-- anyelement is a polymorphic pseudo-type
-- problem with returning 'setof record' is that select * from so_demov3() gives error 
--  ERROR: a column definition list is required for functions returning "record"

drop function so_demov3(anyelement, text, text[]);
create function so_demov3(rec_type anyelement, p_sql text, p_vals text[]) returns setof anyelement  as
$$
declare v_sql text;

begin
  -- edit the executable SQL stmt so that $n -> $1[n]
  v_sql := regexp_replace( p_sql, '[$]([0-9]+)', '$1[\1]', 'g' );
  return query execute v_sql using p_vals;
end;
$$
language plpgsql volatile;

并这样称呼它：

select * from so_demov3(null::text, 'select $1 || $2', array['Barrett', 'Gilmour'] );

这篇关于PostgreSQL传播数组值作为using子句的输入参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！