可変引数のストアード・プロシージャー

以下は、可変引数と $var 値を使用して入力パラメーターのデータ型を取得するストアード・プロシージャーの例です。

CREATE OR REPLACE PROCEDURE sp_varargs01(varargs)
  RETURNS INT4
  LANGUAGE NZPLSQL
  AS 
  BEGIN_PROC
    DECLARE
      num_args int4;
      typ oid;
      idx int4;
    BEGIN
      num_args := PROC_ARGUMENT_TYPES.count;
      RAISE NOTICE 'Number of arguments is %',  num_args;
      for i IN 0 .. PROC_ARGUMENT_TYPES.count - 1 LOOP
        typ := PROC_ARGUMENT_TYPES(i);
        idx := i+1;
        RAISE NOTICE 'argument $% is type % value ''%''',  idx, typ,
                     $idx;
        END LOOP;
    END;
  END_PROC;

sp_varargs01 プロシージャーの呼び出し例を以下に示します。

select sp_varargs01(true, 'test'::char(10), 62443234::int8, 123::int2, 
123456::int4, 34343.4343::float4, 1212.2323::float8, 
'test2'::varchar(10), '2009-05-12'::date, '13:14:05'::time, 
'2009-05-12 08:10:10'::timestamp, '2 days 1 hour'::interval, 
'11:40:36+05'::timetz, 3243.4324234::numeric, 
3243.4324234::numeric(7,2), 5::int1, 'foo'::nchar(20), 
'foo'::nvarchar(20), null::int4);
NOTICE:  Number of arguments is 19
NOTICE:  argument $1 is type 16 value 't'
NOTICE:  argument $2 is type 1042 value 'test      '
NOTICE:  argument $3 is type 20 value '62443234'
NOTICE:  argument $4 is type 21 value '123'
NOTICE:  argument $5 is type 23 value '123456'
NOTICE:  argument $6 is type 700 value '34343.4'
NOTICE:  argument $7 is type 701 value '1212.2323'
NOTICE:  argument $8 is type 1043 value 'test2'
NOTICE:  argument $9 is type 1082 value '2009-05-12'
NOTICE:  argument $10 is type 1083 value '13:14:05'
NOTICE:  argument $11 is type 1184 value '2009-05-12 08:10:10'
NOTICE:  argument $12 is type 1186 value '2 days 01:00:00'
NOTICE:  argument $13 is type 1266 value '11:40:36+05'
NOTICE:  argument $14 is type 1700 value '3243.4324234'
NOTICE:  argument $15 is type 1700 value '3243.43'
NOTICE:  argument $16 is type 2500 value '5'
NOTICE:  argument $17 is type 2522 value 'foo                 '
NOTICE:  argument $18 is type 2530 value 'foo'
NOTICE:  argument $19 is type 23 value '<NULL>'
 sp_varargs01
--------------

(1 row)