將 ROWTYPE 參數傳遞給 EXECUTE

我正在 Postgres 中開發一個函式，旨在為查詢的每條記錄恢復一組函式中包含的檢查結果的值。這些函式中只有一個會返回正確的值。這些函式有一個共同的前綴“fn_condition_”並接收一個“my_table”類型的對像作為參數。

由於進行檢查的函式數量未知，我決定查閱 Postgres 目錄，從表中pg_catalog.pg_proc搜尋前綴為 ‘fn_condition_’ 的函式並使用EXECUTE.

我的問題是傳遞參數的正確形式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;

在 Postgres 8.4 或更高版本中，您將使用USINGof 子句EXECUTE安全有效地傳遞值。這在您的 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 中的搜尋路徑：

• search_path 如何影響標識符解析和“目前模式”

• 只執行 aSELECT而不分配或返回結果是沒有意義的。使用and then的INTO子句。在現代 Postgres 中，您可以將內部循環替換為.EXECUTE``RETURN NEXT``RETURN QUERY EXECUTE
• 在建構動態查詢字元串時，正確使用quote_ident()和轉義標識符和文字。quote_literal()在現代 Postgres 中，您將使用format().
• 將整行轉換為字元串表示、轉義和回退並不是很有效。這種替代方法必須重複從表中讀取，但否則更乾淨（該行直接作為值傳遞）：

 FOR i IN
    VALUES (1), (2), (3)
 LOOP
    EXECUTE 'SELECT ' || quote_ident(_func) || '(t) FROM my_table t WHERE id = ' || i
    INTO _result;
    RETURN NEXT _result;
 END LOOP;

範例函式

您還可以使用此 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;

引用自：https://dba.stackexchange.com/questions/127001