sql - 带有列表参数和 in 子句的 Postgres 函数

Question

如何创建一个函数，该函数将整数 [] 参数作为参数，并在循环中使用带有此参数的 IN 子句执行查询。在循环中，我想执行下一个选择，我想返回这个查询的结果。

类似的东西:

CREATE OR REPLACE FUNCTION function_which_i_want(my_argument integer[]) RETURNS my_schema_and_table[] AS
$BODY$
DECLARE
result my_schema_and_table[];
BEGIN
FOR l IN SELECT * FROM table2 WHERE id in (my_argument) LOOP
SELECT * FROM my_schema_and_table;
END LOOP;
END;
...

我想获得循环中每个选择的并集。一个巨大的联合结果。这可能吗？请帮忙。

Answer 1

PL/pgSQL 函数

它可能看起来像这样:

CREATE OR REPLACE FUNCTION func1(_arr integer[])
  RETURNS SETOF my_schema_and_table
  LANGUAGE plpgsql AS
$func$
DECLARE
   l record;
BEGIN
   FOR l IN
      SELECT *
      FROM   lookup_table
      WHERE  some_id = ANY(_arr)
   LOOP
      RETURN QUERY
      SELECT *
      FROM   my_schema_and_table
      WHERE  link_id = l.link_id;
   END LOOP;
END
$func$;

假设您实际上想要 my_schema_and_table 中的 SET 行，而不是数组？要返回 SELECT * FROM my_schema_and_table 的结果，请将函数声明为 RETURNS SETOF my_schema_and_table

重写 IN构造为 = ANY(_arr) .这就是直接使用数组参数的方法。逻辑等价。
或者使用unnest()并加入结果表，如 @Clodoaldo demonstrates .使用长数组会更快。

简化为纯 SQL 函数

这个简单的 SQL 函数做同样的事情:

CREATE OR REPLACE FUNCTION func2(_arr integer[])
  RETURNS SETOF my_schema_and_table
  LANGUAGE sql AS
$func$
SELECT t.*
FROM  (SELECT unnest($1) AS some_id) x
JOIN   lookup_table l USING (some_id) 
JOIN   my_schema_and_table t USING (link_id);
$func$

假设两个表都有 link_id。

调用:

SELECT * FROM func2('{21,31}'::int[]);