CREATE PROCEDURE (Stored)

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-nameconforms 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.

Improper use of SECURITY DEFINER can lead to the confused deputy problem and introduce vulnerabilities into your system like SQL injection.

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 PLpgSQL 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