如何将多行传递给PostgreSQL函数?

Question

我们如何传递一个(无限数量的)行数组(即常量表)作为PostgreSQL函数的参数/参数？

这里有一个想法：

CREATE TYPE foo AS (
x bigint,
y smallint,
z varchar(64)
);
CREATE OR REPLACE FUNCTION bar(bigint, foo[]) RETURNS TABLE(a bigint, x bigint, y smallint, z varchar(64)) AS
$$
SELECT $1, x, y, z FROM unnest($2);
$$
LANGUAGE SQL;

下面的函数调用有效，但是有没有办法使其更短？

SELECT * FROM bar(1, ARRAY[(1,2,'body1'),(2,1,'body2')]::foo[]);

例如，我们无法删除::foo[]转换，但是有没有办法重写内容以便我们可以省略它？

我们应该使用可变参数吗？

Answer 1

我的谷歌搜索一直把我带到这里，所以我要发布一个答案，这个答案可能不完全符合OP的需求，但可能对看到标题的其他人有所帮助如何将多行传递给PostgreSQL函数？

OP 的原始请求是针对以下类型的：

CREATE TYPE foo AS (
x bigint,
y smallint,
z varchar(64)
);

如果你像我一样，你可能想将标准 SELECT 查询的结果传递给函数。因此，假设我创建了一个表(而不是类型)，如下所示：

CREATE TABLE foo AS (
x bigint,
y smallint,
z varchar(64)
);

我想将以下结果传递给函数：

SELECT * from foo WHERE x = 12345;

结果可能是零行或多行。

根据 postgres 文档 在 https://www.postgresql.org/docs/9.5/static/rowtypes.html 创建一个表还会导致创建具有相同名称的复合类型。这很有帮助，因为这会自动处理原始问题中的CREATE TYPE foo，我现在可以将它作为数组传递给函数。

现在我可以创建一个接受foo类型值数组的函数(简化为关注传入的内容以及记录的使用方式，而不是返回的内容)：

CREATE OR REPLACE FUNCTION bar(someint bigint, foos foo[]) RETURNS ...
LANGUAGE plpgsql
AS $$
DECLARE
foo_record record;
begin
-- We are going to loop through each composite type value in the array
-- The elements of the composite value are referenced just like 
-- the columns in the original table row
FOREACH foo_record IN ARRAY foos LOOP
-- do something, maybe like:
INSERT INTO new_foo (
x, y, z
)
VALUES (
foo_record.x,
foo_record.y,
foo_record.z
);
END LOOP;
RETURN...
END;
$$;

然后，可以使用以下命令非常简单地调用此函数bar(bigint, foo[])：

SELECT bar(4126521, ARRAY(SELECT * from foo WHERE x = 12345));

它将foo表上查询的所有行作为 foo 类型数组传递。然后，我们看到的函数对每一行执行一些操作。

尽管该示例是人为的，也许不完全是OP所问的，但它符合问题的标题，并且可能使其他人不必搜索更多内容以找到他们需要的内容。

编辑命名函数参数使事情变得更容易

Answer 2

PostgreSQL还没有表值变量，所以没有什么会很漂亮。传递数组效率低下，但适用于合理大小的输入。

对于较大的输入，通常有效的方法是传递引用。它很笨拙，但对于较大的数据集(有时与临时表结合使用)可能很实用。

例如

CREATE OR REPLACE FUNCTION bar(i bigint, c refcursor) RETURNS TABLE(a bigint, x bigint, y smallint, z varchar(64)) AS
$$
DECLARE
cursrow foo;
BEGIN
LOOP
FETCH NEXT FROM c INTO cursrow;
a := i;
x := cursrow.x;
y := cursrow.y;
z := cursrow.z;
RETURN NEXT;
IF NOT FOUND THEN
EXIT;
END IF;
END LOOP;
RETURN;
END;
$$;

用法：

demo=> BEGIN;
BEGIN
demo=> DECLARE "curs1" CURSOR FOR VALUES (1,2,'body1'), (2,1,'body2');
DECLARE CURSOR
craig=> SELECT bar(1, 'curs1');
bar      
---------------
(1,1,2,body1)
(1,2,1,body2)
(1,,,)
(3 rows)
demo=> COMMIT;
COMMIT

不漂亮。但是，plpgsql从来都不是。遗憾的是它没有行值的左值，因为能够写出像(x, y, z) := cursrow或ROW(x, y, z) := cursrow这样的东西会让它不那么丑陋。

RETURN NEXT有效，但前提是返回未命名record参数或TABLE。

可悲的是，你不能在子表达式中使用SQL(不是plpgsql)FETCH ALL，所以你不能写

RETURN QUERY NEXT i, cursrow.* FROM (FETCH ALL FROM c) AS cursrow;

Answer 3

似乎问题之一是使用smallint类型，该类型无法从int常量隐式转换。并考虑以下几点：

-- drop function if exists bar(bigint, variadic foo[]);
-- drop type if exists foo;
CREATE TYPE foo AS (
x bigint,
y int, -- change type to integer
z varchar(64)
);
CREATE OR REPLACE FUNCTION bar(bigint, variadic foo[]) RETURNS TABLE(
a bigint,
x bigint,
y int, -- and here
z varchar(64)) AS
$$
SELECT $1, x, y, z FROM unnest($2);
$$
LANGUAGE SQL;
-- Voila! It is even simpler then the using of the ARRAY constructor
SELECT * FROM bar(1, (1,2,'body1'), (2,1,'body2'), (3,4,'taddy bear'));

德布提琴

关于variadic参数