postgresql 带有列表参数和 in 子句的 Postgres 函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12956069/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgres function with list argument and in clause
提问by user1756277
How to create a function which takes as argument integer[] parameter and executing query with IN clause with this parameter in loop. In loop I want execute next select and result of this query I would like return.
如何创建一个函数，该函数将 integer[] 参数作为参数并在循环中使用 IN 子句执行查询。在循环中，我想执行下一个选择并返回这个查询的结果。
Something like that:
类似的东西：
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;
...
I want to get union of each select in loop. one huge joined result. Is this possible? Please help.
我想在循环中获得每个选择的联合。一个巨大的联合结果。这可能吗？请帮忙。
采纳答案by Clodoaldo Neto
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 t.*
from
table2 t
inner join
unnest(my_argument) m(id) on m.id = t.id
loop
SELECT * FROM my_schema_and_table;
END LOOP;
END;
回答by Erwin Brandstetter
PL/pgSQL function
PL/pgSQL 函数
Filling in the gaps in your question, it could look like this:
填补您问题中的空白，它可能如下所示：
CREATE OR REPLACE FUNCTION func1(_arr integer[])
RETURNS SETOF target_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 target_table
WHERE link_id = l.link_id;
END LOOP;
END
$func$;
If you want to return the result of
SELECT * FROM my_schema_and_table;you have to go about this whole function differently. Declare it asRETURNS SETOF target_tableAssuming you actually want a
SETof rows from yourtarget_table, not an array?Rewrite the
INconstruct to= ANY(_arr). That's the way to use an array parameter directly. Internally, PostgreSQL rewrites an IN expression to= ANY()anyway. Test withEXPLAIN ANALYZEto see for yourself.
Oruse the construct withunnest()and join to the resulting table like @Clodoaldo demonstrates. That's faster with long arrays.
如果你想返回结果，
SELECT * FROM my_schema_and_table;你必须以不同的方式处理整个函数。将其声明为RETURNS SETOF target_table假设您实际上想要一个
SET来自您的行target_table，而不是一个数组？将
IN构造重写为= ANY(_arr). 这就是直接使用数组参数的方法。在内部，PostgreSQL 将 IN 表达式改写为= ANY()反正。EXPLAIN ANALYZE亲自测试看看。
或者使用结构 withunnest()并连接到结果表，如@Clodoaldo 演示。长数组更快。
Simplify to plain SQL function
简化为纯 SQL 函数
The above is still pointlessly contrived. Simplify to a SQL function doing the same:
以上仍然是毫无意义的做作。简化为执行相同操作的 SQL 函数：
CREATE OR REPLACE FUNCTION func2(_arr integer[])
RETURNS SETOF target_table LANGUAGE sql AS
$func$
SELECT t.*
FROM (SELECT unnest() AS some_id) x
JOIN lookup_table l USING (some_id)
JOIN target_table t USING (link_id); -- assuming both tables have link_id
$func$
Call:
称呼：
SELECT * FROM func2('{21,31}'::int[]);
