如何将多行传递给PostgreSQL函数? [英] How to pass multiple rows to PostgreSQL function?
问题描述
我们如何传递一个(无限量)行数组(即一个常量表)作为PostgreSQL函数的参数/参数?
这里是一个想法:
$ b $ pre $ code CREATE TYPE foo AS
igint,
y smallint,
z varchar 64)
);
创建或替换函数bar(bigint,foo [])返回TABLE(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 []
cast,但有没有办法重写一些东西,以便我们省略它?
我们应该使用variatic参数吗?
看来,其中一个问题是使用 smallint
类型,它不能从 int
常量隐式转换。并考虑以下几点:
- drop function if exists bar(bigint,variadic foo []);
- drop type if foo;
CREATE TYPE foo AS(
x bigint,
y int, - 将类型更改为整数
z varchar(64)
);
创建或替换函数bar(bigint,variadic foo [])RETURNS TABLE(
a bigint,
int bigint,
y int, - and here
z varchar(64))AS
$$
SELECT $ 1,x,y,z FROM unnest($ 2);
$$
LANGUAGE SQL;
- 瞧!它比使用ARRAY构造函数更简单
SELECT * FROM bar(1,(1,2,'body1'),(2,1,'body2'),(3,4,'taddy bear ));
How can we pass an array of (an unlimited amount of) rows (ie, a constant table) as the parameter/argument of a PostgreSQL function?
Here's an idea:
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;
The below function call works, but is there a way to make it shorter?
SELECT * FROM bar(1, ARRAY[(1,2,'body1'),(2,1,'body2')]::foo[]);
For example, we can't remove the ::foo[]
cast, but is there a way to rewrite things so that we can omit it?
Should we be using a variatic argument?
It seems that one of the problems is the using of smallint
type which can not be converted implicitly from an int
constants. And consider the following:
-- 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'));
这篇关于如何将多行传递给PostgreSQL函数?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!