Postgresql中的变长参数类型VARIADIC实例与限制

Postgresql支持变长参数传递，参数被自动转换为数据传入函数体中，类似C语言的可变参数：int sum(int num_args, ...)。

0 定义与执行限制

1 VARIADIC实例

VARIADIC类型将入参转为数组使用，数据下标从一开始

CREATE or replace PROCEDURE var_test1(VARIADIC arr int[])
LANGUAGE plpgsql
AS $$
DECLARE
    i int;
BEGIN
    raise notice 'ndims: %', array_ndims(arr);
    raise notice 'len: %', array_length(arr, 1);
    raise notice 'lower bound: %', array_lower(arr, 1);
    for i in 1..array_length(arr,1) loop
      raise notice '%', arr[i];
    end loop;
END;
$$;

call var_test1(2,34,55,66);
call var_test1(VARIADIC ARRAY[2,34,55,66]);
call var_test1(VARIADIC arr := ARRAY[2,34,55,66]);

执行结果

postgres=*# 
call var_test1(2,34,55postgres=*# call var_test1(2,34,55,66);
NOTICE:  ndims: 1
NOTICE:  len: 4
NOTICE:  lower bound: 1
NOTICE:  2
NOTICE:  34
NOTICE:  55
NOTICE:  66
CALL
postgres=*# call var_test1(VARIADIC ARRAY[2,34,55,66]);
NOTICE:  ndims: 1
NOTICE:  len: 4
NOTICE:  lower bound: 1
NOTICE:  2
NOTICE:  34
NOTICE:  55
NOTICE:  66
CALL
postgres=*# call var_test1(VARIADIC arr := ARRAY[2,34,55,66]);
NOTICE:  ndims: 1
NOTICE:  len: 4
NOTICE:  lower bound: 1
NOTICE:  2
NOTICE:  34
NOTICE:  55
NOTICE:  66
CALL

游标（from digoal）

CREATE FUNCTION var_test2(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 relname from pg_class;  
   return next res;  
 end loop;  
end;  
$$ lANGUAGE plpgsql; 

begin;
select * from var_test2('a','b');  
fetch 1 in a;
commit;

结果

postgres=# begin;
BEGIN
postgres=*# select * from var_test2('a','b');  
 var_test2 
-----------
 a
 b
(2 rows)

postgres=*# fetch 1 in a;
 relname 
---------
 f2
(1 row)

2 定义方式限制（黑盒探索）

定义多个VARIADIC（失败）

CREATE or replace PROCEDURE var_test1(VARIADIC arr int[], VARIADIC arr1 int[])
LANGUAGE plpgsql
AS $$
DECLARE
    i int;
BEGIN
    raise notice 'ndims: %', array_ndims(arr);
    raise notice 'len: %', array_length(arr, 1);
    raise notice 'lower bound: %', array_lower(arr, 1);
    for i in 1..array_length(arr,1) loop
      raise notice '%', arr[i];
    end loop;
END;
$$;


ERROR:  VARIADIC parameter must be the last input parameter

VARIADIC前面放普通参数（成功）普通参数匹配后剩下的给VARIADIC

CREATE or replace PROCEDURE var_test1(t1 int, VARIADIC arr int[])
LANGUAGE plpgsql
AS $$
DECLARE
    i int;
BEGIN
    raise notice 'ndims: %', array_ndims(arr);
    raise notice 'len: %', array_length(arr, 1);
    raise notice 'lower bound: %', array_lower(arr, 1);
    for i in 1..array_length(arr,1) loop
      raise notice '%', arr[i];
    end loop;
END;
$$;
call var_test1(2,34,55,66);

VARIADIC后面放普通参数（失败）

CREATE or replace PROCEDURE var_test2(VARIADIC arr int[], t1 int)
LANGUAGE plpgsql
AS $$
DECLARE
    i int;
BEGIN
    raise notice 'ndims: %', array_ndims(arr);
    raise notice 'len: %', array_length(arr, 1);
    raise notice 'lower bound: %', array_lower(arr, 1);
    for i in 1..array_length(arr,1) loop
      raise notice '%', arr[i];
    end loop;
END;
$$;
ERROR:  VARIADIC parameter must be the last input parameter

VARIADIC前面放默认值普通参数、无默认参数（失败）

CREATE or replace PROCEDURE var_test5(t1 int default 10, t2 int, VARIADIC arr int[])
LANGUAGE plpgsql
AS $$
DECLARE
    i int;
BEGIN
    raise notice 'ndims: %', array_ndims(arr);
    raise notice 'len: %', array_length(arr, 1);
    raise notice 'lower bound: %', array_lower(arr, 1);
    for i in 1..array_length(arr,1) loop
      raise notice '%', arr[i];
    end loop;
    raise notice 't1: %', t1;
END;
$$;
call var_test5(1,2,3);

ERROR:  input parameters after one with a default value must also have defaults

3 调用方式限制

VARIADIC必须接收至少一个参数

CREATE or replace PROCEDURE var_test3(VARIADIC arr int[])
LANGUAGE plpgsql
AS $$
DECLARE
    i int;
BEGIN
    raise notice 'ndims: %', array_ndims(arr);
    raise notice 'len: %', array_length(arr, 1);
    raise notice 'lower bound: %', array_lower(arr, 1);
    for i in 1..array_length(arr,1) loop
      raise notice '%', arr[i];
    end loop;
END;
$$;
call var_test3();

ERROR:  procedure var_test3() does not exist

定向传参无法用到VARIADIC

CREATE or replace PROCEDURE var_test4(q int default 199, VARIADIC arr int[])
LANGUAGE plpgsql
AS $$
BEGIN
    raise notice 'ndims: %', array_ndims(arr);
    raise notice 'len: %', array_length(arr, 1);
    raise notice 'lower bound: %', array_lower(arr, 1);
    for i in 1..array_length(arr,1) loop
      raise notice '%', arr[i];
    end loop;
    raise notice 'q: %', q;
END;
$$;
-- call var_test4(1, VARIADIC arr := ARRAY[2,34,55,66]);
-- call var_test4(1,2,34,55,66);
-- call var_test4(1, VARIADIC ARRAY[2,34,55,66]);

call var_test4(arr => VARIADIC ARRAY[2,34,55,66]);
ERROR:  procedure var_test4(arr => integer[]) does not exist

call var_test4(1, arr => ARRAY[2,34,55,66]);
ERROR:  procedure var_test4(integer, arr => integer[]) does not exist

同名函数存在，优先使用非VARIADIC函数

CREATE or replace PROCEDURE var_test5(VARIADIC arr int[])
LANGUAGE plpgsql
AS $$
BEGIN
    raise notice 'ndims: %', array_ndims(arr);
    raise notice 'len: %', array_length(arr, 1);
    raise notice 'lower bound: %', array_lower(arr, 1);
    for i in 1..array_length(arr,1) loop
      raise notice '%', arr[i];
    end loop;
END;
$$;

CREATE or replace PROCEDURE var_test5(x int)
LANGUAGE plpgsql
AS $$
BEGIN
    raise notice 'x: %', x;
END;
$$;

call var_test5(2);
call var_test5(2,34,55,66);
call var_test5(VARIADIC array[2]);



-- 结果
ss1=# call var_test5(2);
NOTICE:  x: 2
CALL

ss1=# call var_test5(2,34,55,66);
NOTICE:  ndims: 1
NOTICE:  len: 4
NOTICE:  lower bound: 1
NOTICE:  2
NOTICE:  34
NOTICE:  55
NOTICE:  66
CALL

ss1=# call var_test5(VARIADIC array[2]);
NOTICE:  ndims: 1
NOTICE:  len: 1
NOTICE:  lower bound: 1
NOTICE:  2
CALL