Argument list and variables

The arguments passed as input to procedures can be identified by using the names $1 (first argument), $2 (second argument), and so on. You can also use the $var notation, which takes the integer value of var to identify the input value specified in that order position.

You can specify 0 - 64 arguments. If you specify VARARGS in the input argument list, users can input any number of arguments up to a maximum of 64.

Netezza Performance Server saves the OIDs of the input arguments in the PROC_ARGUMENT_TYPES array. The array has the same size as the number of parameters that are passed to the stored procedure. Its elements are of type OID and contain the OID type of the corresponding input argument. You can use the array to obtain the number and type of each argument.

For example:

CREATE OR REPLACE PROCEDURE test(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: %',  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 % and has the value ''%''',  
idx, typ, $idx;
        END LOOP;
      END;
    END_PROC;

A sample call follows:

MYDB.SCHEMA(USR2)=> CALL TEST (1, 3, 9::bigint, 'test value', 324.56);
NOTICE:  Number of arguments: 5
NOTICE:  argument $1 is type 23 and has the value '1'
NOTICE:  argument $2 is type 23 and has the value '3'
NOTICE:  argument $3 is type 20 and has the value '9'
NOTICE:  argument $4 is type 705 and has the value 'test value'
NOTICE:  argument $5 is type 1700 and has the value '324.56'
 test
------

(1 row)