Parameter modes

Each formal parameter of a stored procedure can be set to one of the following parameter modes. If unspecified, the parameter is set to IN:

Each formal parameter of a stored procedure can be set to one of the following parameter modes. If unspecified, the parameter is set to IN:

  • IN: An input parameter. The caller uses this to pass arguments to the stored procedure.
  • OUT: An output parameter. The stored procedure returns values as a result set, the columns of which are identified by the name of the output parameter.
  • INOUT: An input and output parameter. The caller can both pass an argument to the stored procedure and retrieve it from the result set.

Function signatures and overloading

A function's signature is defined by the function's name and input parameter (IN and INOUT) types. You cannot create two stored procedures with the same function signature.

An overloaded function is a set of functions that share the same name but have different input parameters. This can be useful in cases where a function needs to operate on multiple types. For example, the two functions find_average(int, int, int) and find_average(float, float, float) describe the overloaded function find_average() that operates on integers and floats.

When an overloaded procedure is called, Vertica runs the procedure whose signature matches the types of the arguments passed in the invocation.

The example procedures below use RAISE NOTICE for informational messages. For details, see Errors and diagnostics.

IN

IN parameters specify the name and type of an argument. For example, the caller of this procedure must pass in an INT and a VARCHAR value:

=> CREATE PROCEDURE raiseXY(IN x INT, y VARCHAR) LANGUAGE PLvSQL AS $$
BEGIN
    RAISE NOTICE 'x = %', x;
    RAISE NOTICE 'y = %', y;
END
$$;

CALL raiseXY(3, 'some string');
NOTICE 2005:  x = 3
NOTICE 2005:  y = some string

OUT

OUT parameters specify the name and type of a return value. When the procedure runs, the OUT parameter is added to the scope of the stored procedure as a variable. This variable's value is initialized as NULL and is returned in a result set after execution:

CREATE PROCEDURE sum_procedure(IN x INT, IN y INT, OUT z INT) LANGUAGE PLvSQL AS $$
BEGIN
    RAISE NOTICE 'This procedure returns the sum of x and y as z:';
    z := x + y;
END
$$;

=> CALL sum_procedure(38,19);
NOTICE 2005:  This procedure returns the sum of x and y as z:
 z
----
 57
(1 row)

INOUT

INOUT parameters specify the name and type of both an input value and return value. When the procedure runs, the IN/OUT parameter is initialized to the value passed in by the caller and is returned in a result set after execution:

=> CREATE PROCEDURE echo(INOUT x INT) LANGUAGE PLvSQL AS $$
BEGIN
    RAISE NOTICE 'This procedure returns its input:';
END
$$;

=> CALL echo(19);
NOTICE 2005:  This procedure returns its input:
 x
----
 19
(1 row)