执行时传递ROWTYPE参数

Question

我正在Postgres中开发一个函数，该函数旨在为查询的每个记录恢复一组函数中包含的检查结果的值。这些函数中只有一个将返回正确的值。这些函数有一个comun前缀'fn_condicao_‘，并作为参数接收'my_table’类型的对象。

由于进行检查的函数数量未知，我决定查阅Postgres目录，在表pg_catalog.pg_proc中搜索前缀为'fn_condicao_‘的函数，然后使用EXECUTE动态执行它们。

我的问题是如何为执行传递正确的形状参数。

create or replace function test_conditions()
returns void as 
$$
declare
    v_record my_table%rowtype;
    v_function pg_proc%rowtype;    
begin 
    set search_path = 'pg_catalog';

    for v_record in (select * from my_table where id in (1,2,3)) loop
        for v_function in (
            SELECT  p.proname
            FROM    pg_namespace n
            JOIN    pg_proc p
            ON      p.pronamespace = n.oid
            WHERE   n.nspname = 'operacional'
            and p.proname like ('fn_condition\\_%')
            order by p.proname) 
        loop
            --execute 'select ' || v_function.proname || '(' || v_record || ')';
        end loop;
    end loop;
end;
$$ 
language plpgsql;

如何在上面函数中注释的v_record命令中正确地传递EXECUTE？

execute 'select ' || v_function.proname || '(' || v_record || ')';  -- ???

示例函数：

create or replace function fn_condition_1(p_record my_table)
returns bigint as 
$$
begin 
    if ($1.atributo1 > $1.atributo2) then
        return 1;
    end if;
    return null;
end;
$$ 
language plpgsql;

Answer 1

这个问题在DataBase管理员中由埃尔文·勃兰特回答。所以，我想和大家分享这个决议。

DataBase管理员回答

在Postgres8.4或更高版本中，您将使用EXECUTE的USING子句安全有效地传递值。这在您的版本8.3中是不可用的。在您的版本中，它可以这样工作：

CREATE OR REPLACE FUNCTION test_conditions()
  RETURNS SETOF bigint AS
$func$
DECLARE
   _rec    record;
   _func   text;
   _result bigint;
BEGIN
   FOR _func in
      SELECT  p.proname
      FROM    pg_catalog.pg_namespace n
      JOIN    pg_catalog.pg_proc      p ON p.pronamespace = n.oid
      WHERE   n.nspname = 'operacional'
      AND     p.proname LIKE E'fn\\_condition\\_%'  -- no parens, proper string
      ORDER   BY p.proname  -- no parens
   LOOP
      FOR _rec in
         SELECT * FROM my_table WHERE id IN (1,2,3)  -- no parens needed
      LOOP
         EXECUTE 'SELECT ' || quote_ident(_func) || '(' || quote_literal(_rec) || ')'
         INTO _result;
         RETURN NEXT _result;
      END LOOP;
   END LOOP;
END
$func$  LANGUAGE plpgsql SET search_path = 'public';

呼叫：

SELECT * FROM test_conditions();

• 如果在函数体中使用set search_path = 'pg_catalog';，那么public模式中的表将不再可见。这将是一个非常糟糕的想法，在全球SET搜索路径。该效果在设置的持续时间内保持不变。您可以使用SET LOCAL将其包含到事务中，但这仍然是个坏主意。相反，如果确实需要，只设置函数的环境，就像演示的那样。 更多关于Postgres中搜索路径的信息：

- [How does the search\_path influence identifier resolution and the “current schema”](https://stackoverflow.com/a/9067777/939860)

​

• 只执行一个SELECT而不分配或返回结果就没有意义了。使用INTO子句EXECUTE，然后使用RETURN NEXT。在现代的Postgres中，您将用RETURN QUERY EXECUTE替换内部循环。
• 在构建动态查询字符串时，使用quote_ident()和quote_literal()正确地转义标识符和文字。在现代的Postgres中，您将使用format()。
• 将整个行转换为它的字符串表示、转义和回滚并不是很有效。这种替代方法必须反复从表中读取，但如果不是这样，则更干净(行作为值直接传递)： 对于i IN值(1)，(2)，(3)循环执行从quote_ident t中的my_table t中的'SELECT‘而开(_func)欧元(X)\n(T)，其中id =’END \i IN _result；返回NEXT _result；END循环；

示例函数

您还可以使用此SQL函数从根本上简化示例函数：

CREATE OR REPLACE FUNCTION fn_condition_1(p_record my_table)
  RETURNS bigint AS
$func$
   SELECT CASE WHEN $1.atributo1 > $1.atributo2 THEN bigint '1' END
$func$  LANGUAGE sql;

Answer 2

我相信您的问题是，函数中的execute命令试图插值v_record的值，这实际上将其转换为一个离散的参数列表，而不是函数所期望的原生行类型。

如果您愿意更改每个函数的参数类型，这可能是处理此问题的最简单方法。如果不是，则需要某种方式将本机行类型传递给动态函数调用。虽然这看起来很糟糕，但我认为这样的事情会奏效的：

create or replace function test_conditions()
returns void as 
$$
declare
    v_record my_table%rowtype;
    v_function pg_proc%rowtype;    
begin 
    set search_path = 'pg_catalog';

    for v_record in (select * from my_table where id in (1,2,3)) loop
        for v_function in (
            SELECT  p.proname
            FROM    pg_namespace n
            JOIN    pg_proc p
            ON      p.pronamespace = n.oid
            WHERE   n.nspname = 'operacional'
            and p.proname like ('fn_condition_%')
            order by p.proname) 
        loop
          execute '
            do $ZOOM$
              declare
                v_rec my_table%rowtype;
              begin
                select *
                into v_rec
                from my_table
                where id = ' || v_record.id || ';

                perform ' || func_name || '(v_rec);
              end;
            $ZOOM$
          ';
        end loop;
    end loop;
end;
$$ 

另外，我认为您需要将您的select更改为perform (如上面所示).要么这样，要么做一个select into。