PostgresSQL 使用數組中的參數值動態執行

我想知道這在 Postgres 中是否可行：

最好用一個人為的例子來解釋：

create or replace function test_function(filter_param1 varchar default null
                                      , filter_param2 varchar default null)
 returns integer as
$$ 
declare
 stmt text;
 args varchar[];
 wher varchar[];
 retid integer;
begin
 if filter_param1 is not null then 
   array_append(args, filter_param1);
   array_append(wher, 'parameter_name = $1');
 end if;
 if filter_param2 is not null then 
   array_append(args, filter_param2);
   array_append(wher, 'parameter_name = $2');
 end if;

 stmt := 'select id from mytable where ' || array_to_string(wher, ' or ');
 execute stmt into retid using args;

 return retid;
end;
$$ language plpgsql;

在 Python 中有*args- 也許 PostgreSQL 有類似的機制？

編輯 Erwin Brandstetter 問題：

• 所有filter參數都將應用於不同的列，但應該是 AND’ed。
• 回來setof在這裡更有意義。
• 所有參數可以是相同的列類型（即。varchar）。

無論哪種方式，這都是完全可能的，因為您的所有參數都是相同的數據類型。

EXECUTE ... USING愉快地接受一個數組，該數組被視為單個參數。使用數組下標訪問元素。

create or replace function test_function(_filter1 text = null
                                      , _filter2 text = null
                                      , OUT retid int) as
$func$
declare
  **_args text[] := ARRAY[_filter1, _filter2];**
  _wher text[];
begin
  if _filter1 is not null then 
     _wher := _wher || 'parameter_name = **$1[1]**'; -- note array subscript
  end if;

  if _filter2 is not null then 
     _wher := _wher || 'parameter_name = **$1[2]**'; -- assign the result!
  end if;
 
  IF _args  IS NULL         -- check whether all params are NULL
     RAISE EXCEPTION 'At least one parameter required!';
  END IF;

  execute 'select id from mytable where ' -- cover case with all params NULL
        || array_to_string(_wher, ' or ')
        || ' ORDER BY id LIMIT 1';   -- For a single value (???)
  into  retid
  **using _args**;
end
$func$  language plpgsql;

這只是一個概念證明，不必要地複雜。對於實際的數組輸入，例如使用VARIADIC函式，這將是一個有趣的選項。例子：

• 如何在 plpgsql 中執行 WHERE x IN (val1, val2,…)

對於手頭的情況，請改用：

CREATE OR REPLACE FUNCTION test_function(_filter1 text = null
                                      , _filter2 text = null)
 RETURNS SETOF int AS
$func$
DECLARE
  _wher text := concat_ws(' OR '
            , CASE WHEN _filter1 IS NOT NULL THEN 'parameter_name = **$1**' END
            , CASE WHEN _filter2 IS NOT NULL THEN 'parameter_name = **$2**' END);
BEGIN
  IF _wher = ''   -- check whether all params are NULL
     RAISE EXCEPTION 'At least one parameter required!';
  END IF;

  RETURN QUERY EXECUTE 'SELECT id FROM mytable WHERE ' || _wher
  **USING $1, $2**;
  -- USING  _filter1 , filter2; -- alternatively use func param names
END
$func$  LANGUAGE plpgsql;

解釋

• 按出現順序列出所有可能在USING子句中的動態查詢中引用的值。如果不是所有這些都將在動態查詢中被引用，那也沒有什麼壞處。但是我們需要保持序數位置不變。
• 請特別注意，在動態查詢中，通過序號引用子句的給定值，而在**子句中引用函式參數。相同的語法，不同的範圍！ 在我的範例中為簡單起見參考。但是可以以任何方式重新排序子句中的值，以便（例如）在動態查詢中引用子句中的第二個位置，該位置引用第一個函式參數。$n USING$n USING
$2``$2``USING``$2``$1``USING
• 這允許具有任何**（異構）數據類型的**任意數量的參數。
• 在這個例子（）中返回一組整數**RETURNS SETOF int**，它更適合這個例子 -RETURN QUERY EXECUTE相應地使用。
• concat_ws()有條件地組裝 OR’ed 或 AND’ed 謂詞列表特別方便。

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