CREATE PROCEDURE (stored)

Creates a stored procedure.

Creates a stored procedure.

Syntax

CREATE [ OR REPLACE ] PROCEDURE [ IF NOT EXISTS ]
    [[database.]schema.]procedure( [ parameter-list ] )
    [ LANGUAGE 'language-name' ]
    [ SECURITY { DEFINER | INVOKER } ]
    AS $$ source $$;

Parameters

OR REPLACE
If a procedure with the same name already exists, replace it. Users and roles with privileges on the original procedure retain these privileges on the new procedure.

This option cannot be used with IF NOT EXISTS.

IF NOT EXISTS

If an object with the same name exists, do not create it and proceed. If you omit this option and the object exists, Vertica generates a ROLLBACK error message. In both cases, the object is not created if it already exists.

The IF NOT EXISTS clause is useful for SQL scripts where you want to create an object if it does not already exist.

For related information, see ON_ERROR_STOP.

This option cannot be used with OR REPLACE.

[database.]schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.

procedure
The name of the stored procedure, where *procedure-name*conforms to conventions described in Identifiers.
parameter-list
A comma-delimited list of formal parameters, each specified as follows: 
[ parameter-mode ] parameter-name parameter-type

  • parameter-name: the name of the parameter.

  • parameter-type: Any SQL data type, with the following exceptions:

    • DECIMAL

    • NUMERIC

    • NUMBER

    • MONEY

    • UUID

    • GEOGRAPHY

    • GEOMETRY

    • Complex types

language-name
Specifies the language of the procedure source, one of the following (both options refer to PLvSQL; PLpgSQL is included to maintain compatibility with existing scripts):

  • PLvSQL

  • PLpgSQL

Default: PLvSQL

SECURITY { DEFINER | INVOKER }
Determines whose privileges to use when the procedure is called and executes it as if the user is one of the following:

  • DEFINER: User who defined the procedure

  • INVOKER: User who called the procedure

A procedure with SECURITY DEFINER effectively executes the procedure as that user, so changes to the database appear to be performed by the procedure's definer rather than its caller.

For more information, see Executing stored procedures.

source
The procedure source code. For details, see Scope and structure.

Privileges

Non-superuser: CREATE on the procedure's schema

Examples

For more complex examples, see Stored procedures: use cases and examples

This procedure prints its arguments:

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

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

For more information on RAISE NOTICE, see Errors and diagnostics.

See also