PostgreSQL plpgsql variadic argments , parameters - 可变参数个数
背景
PostgreSQL 函数支持可变个数的参数，有很多聚合函数就是可变参数的，可变参数在一些场景非常有效，能解决一些特殊的问题，帮助简化代码.
正文
但是使用可变参数, 有一定的要求. 首先可变参数必须放在函数参数列表的末尾部位, 另外就是可变参数使用时定义为数组，所以参数类型必须一致.
例如 :
digoal=> CREATE FUNCTION mleast(VARIADIC arr numeric[]) RETURNS numeric AS $$
digoal$> SELECT min($1[i]) FROM generate_subscripts($1, 1) g(i);
digoal$> $$ LANGUAGE SQL;
CREATE FUNCTION
变长参数函数的调用方法如下 :
digoal=> select mleast(1,2,3);
mleast
--------
1
(1 row)
digoal=> SELECT mleast(VARIADIC ARRAY[1,2,3]);
mleast
--------
1
(1 row)
digoal=> SELECT mleast(VARIADIC arr := ARRAY[1,2,3]);
mleast
--------
1
(1 row)
前面提到VARIADIC参数必须放在所有参数的末尾, 如果不是末尾将报错
digoal=> CREATE FUNCTION mleast(VARIADIC arr numeric[], info text) RETURNS numeric AS $$
SELECT min($1[i]) FROM generate_subscripts($1, 1) g(i);
$$ LANGUAGE SQL;
ERROR: VARIADIC parameter must be the last input parameter
放到末尾正常 :
digoal=> create or replace function mleast(info text, VARIADIC arr numeric[]) RETURNS numeric AS $$
digoal$> declare
digoal$> result numeric := null;
digoal$> begin
digoal$> raise notice '%', info;
digoal$> SELECT min(arr[i]) into result FROM generate_subscripts(arr, 1) g(i);
digoal$> return result;
digoal$> end;
digoal$> $$ language plpgsql strict;
CREATE FUNCTION
digoal=> select * from mleast('hello', 1.1, 3.0, -1.1, 9.9);
NOTICE: hello
mleast
--------
-1.1
(1 row)
同时variadic参数至少输入1个参数, 否则函数是不匹配的 :
digoal=> select * from mleast('hello');
ERROR: function mleast(unknown) does not exist
LINE 1: select * from mleast('hello');
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
digoal=> select * from mleast('hello',null);
NOTICE: hello
mleast
--------
(1 row)
又如 :
postgres=# create or replace function avg(VARIADIC arr numeric[]) RETURNS numeric AS $$
declare
result numeric := null;
begin
SELECT avg(arr[i]) into result FROM generate_subscripts(arr, 1) g(i);
return result;
end;
$$ language plpgsql strict;
CREATE FUNCTION
postgres=# select * from avg(1,2,3,4,5,6,7,8,9);
avg
--------------------
5.0000000000000000
(1 row)
不支持多个不定长数组.
postgres=# create or replace function avg(VARIADIC arr1 numeric[], VARIADIC arr2 text[]) RETURNS numeric AS $$
declare
result numeric := null;
begin
SELECT avg(arr[i]) into result FROM generate_subscripts(arr, 1) g(i);
return result;
end;
$$ language plpgsql strict;
ERROR: VARIADIC parameter must be the last input parameter
如果需要用到varidic的refcursor, 需要使用一些手段，不要直接使用open arr[slice] 来打开游标
postgres=# CREATE FUNCTION myfunc(variadic refcursor[]) RETURNS SETOF refcursor AS $$
begin
open $1[1] for select * from pg_class;
return next $1[1];
open $1[2] for select * from pg_class;
return next $1[2];
end;
$$ lANGUAGE plpgsql;
ERROR: 42601: syntax error, expected "FOR" at or near "["
LINE 3: open $1[1] for select * from pg_class;
^
LOCATION: plpgsql_yyerror, pl_scanner.c:639
Time: 0.734 ms
可以使用变量消除这个语法错误
postgres=# CREATE FUNCTION myfunc(variadic refcursor[]) RETURNS SETOF refcursor AS $$
declare
res refcursor;
begin
for x in 1..array_length($1,1) loop
res := $1[x];
open res for select * from pg_class;
return next res;
end loop;
end;
$$ lANGUAGE plpgsql;
postgres=# select * from myfunc('a','b');
myfunc
--------
a
b
(2 rows)
Time: 0.930 ms
postgres=# fetch 1 in a;
relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | relhasindex | relisshared | relpersistence | relkind | relnatts | relchecks |
relhasoids | relhaspkey | relhasrules | relhastriggers | relhassubclass | relrowsecurity | relforcerowsecurity | relispopulated | relreplident | relfrozenxid | relminmxid | relacl | reloptions
-----------------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+----------------+---------+----------+-----------+
------------+------------+-------------+----------------+----------------+----------------+---------------------+----------------+--------------+--------------+------------+--------+------------
pg_toast_252744 | 99 | 252748 | 0 | 10 | 0 | 252747 | 0 | 0 | 0 | 0 | 0 | t | f | p | t | 3 | 0 |
f | t | f | f | f | f | f | t | n | 386118340 | 1 | |
(1 row)
Time: 0.687 ms
postgres=# fetch 1 in b;
relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | relhasindex | relisshared | relpersistence | relkind | relnatts | relchecks |
relhasoids | relhaspkey | relhasrules | relhastriggers | relhassubclass | relrowsecurity | relforcerowsecurity | relispopulated | relreplident | relfrozenxid | relminmxid | relacl | reloptions
-----------------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+----------------+---------+----------+-----------+
------------+------------+-------------+----------------+----------------+----------------+---------------------+----------------+--------------+--------------+------------+--------+------------
pg_toast_252744 | 99 | 252748 | 0 | 10 | 0 | 252747 | 0 | 0 | 0 | 0 | 0 | t | f | p | t | 3 | 0 |
f | t | f | f | f | f | f | t | n | 386118340 | 1 | |
(1 row)
Time: 0.669 ms
如果你不想传参，也可以在函数内直接生成游标数组，然后使用变量消除open array[slice]的语法问题。
CREATE FUNCTION myfunc(int) RETURNS SETOF refcursor AS $$
declare
ref refcursor[];
res refcursor;
begin
for x in 1..$1 loop
res := $1[x];
open res for select * from pg_class;
return next res;
end loop;
end;
$$ lANGUAGE plpgsql;
参考
1. http://www.postgresql.org/docs/devel/static/xfunc-sql.html#XFUNC-SQL-VARIADIC-FUNCTIONS
2. http://www.postgresql.org/docs/devel/static/plpgsql-overview.html
3. http://blog.163.com/digoal@126/blog/static/1638770402012714102818672/