Skip to main content

CREATE PROCEDURE

Creates a procedure.

important

Queries involving procedures created by CREATE PROCEDURE can only be run on Hybrid DPS clusters, not on Extreme DPS clusters.

Syntax​

CREATE [OR REPLACE] PROCEDURE <name>    
    ( [ [<arg_mode>] [<arg_name>] <arg_type> [ { DEFAULT | = } <default_expr> ] [, ...] ] )
  { LANGUAGE <lang_name>
    | TRANSFORM { FOR TYPE <type_name> } [, ... ]
    | { [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER }
    | SET <config_param> { TO <value> | = <value> | FROM CURRENT }
    | AS '<definition>'
  } ...

Description​

To create a procedure, you must have the USAGE privilege on the argument types. You are the owner of the procedures that you create, by default.

Relyt uses the procedure name and the input argument types to uniquely identify a procedure. Therefore, procedures with the same name but different input argument types can exist in the same schema.

CREATE PROCEDURE can only be used to create a procedure. If you want to replace an existing one, use CREATE OR REPLACE PROCEDURE.

Only the owner of a procedure can run CREATE OR REPLACE PROCEDURE to replace the procedure. After the command is run, the owner and the permissions of the procedure remain the same, while the other parameters are using the values specified in the command.

For more information about the creating a procedure, see User Defined Procedures in the PostgreSQL documentation.

info

If you want to update the name or argument types of a procedure, drop the procedure and recreate it. If you try to use CREATE OR REPLACE PROCEDURE to achieve this, a new procedure will be created, instead of replacing the existing one.

Parameters​

  • <name>

    The name of the procedure. You can specify the name with the schema qualification to create the procedure in the specified schema. Otherwise, the procedure is created in the current schema.

  • <arg_mode>

    The mode of the argument. Available values are IN, INOUT, and VARIADIC. If you omit this parameter, the default value IN is used.

    Currently, procedures do not support OUT arguments. Use INOUT instead.

  • <arg_name>

    The name of the argument.

  • <arg_type>

    The data type of the argument. You can specify the data type with the schema qualification. Supported data types can be base, composite, and domain types, and can reference from the data type of a table column.

    Pseudotypes are also allowed for certain implementation languages, so that you can specify pseudotypes such as cstring. Pseudotypes indicate that the actual argument type is either incompletely specified, or outside the set of ordinary SQL data types.

    When you reference the data type of a column, specify it in the table_name.column_name%TYPE format. By doing this, the procedure you create can be independent from the changes to the definition of a table.

  • <default_expr>

    The expression to generate the default value if the parameter is not specified. The expression must be coercible to the argument type of the parameter. Note that only IN and INOUT parameters can have default values.

    In the argument list, each IN or INOUT parameter that follows a parameter with a default value must have a default value.

  • <lang_name>

    The name of the language in which the procedure is implemented. The value can be SQL, C, internal, or the name of a user-defined procedural language.

  • TRANSFORM { FOR TYPE <type_name> } [, ... ]

    The list of transforms to which a call to the procedure must apply. A transform is used to convert between an SQL type and a language-specific data type. In most cases, built-in types are hard-coded in procedural language implementations. Therefore, this parameter is unnecessary. Even if the specific procedural implementation does not know how to handle a type and no transform is provided, it will use a default behavior when converting data types. The default behavior varies with the implementation.

  • [EXTERNAL] SECURITY INVOKER or [EXTERNAL] SECURITY DEFINER

    The user of which the privileges are required for running the procedure.

    • SECURITY INVOKER: the user who calls the procedure.
    • SECURITY DEFINER: the user who creates the procedure.

    Keyword EXTERNAL is optional because this option is available for all procedures in Relyt, instead of only external ones. You can specify it to conform to the SQL standard.

  • <config_param> and <value>

    You can use the SET clause to specify a value for a session configuration parameter for the procedure. The configuration parameter setting specified in this way is effective only for the current session. When the procedure exits, the value of the configuration parameter is restored to the original value.

    SET FROM CURRENT saves the current value of the configuration parameter as the value to be applied when the procedure is entered.

    If a procedure has a SET clause, any SET LOCAL command executed inside the procedure for the same variable only applies to the procedure. Once the procedure exits, the configuration parameter will be restored to its original value. However, an ordinary SET command (without LOCAL) overrides the SET clause, just as it will for a previous SET LOCAL command. The effects of this command will persist after the procedure exits, unless the current transaction is rolled back.

    For more details, see SET.

  • <definition>

    The definition of the procedure. The value must be a string constant that indicates the internal procedure name, the path to an object file, an SQL command, or text based on the language used to implement the procedure.

    We recommend that you use dollar quoting to specify the value of definition, or you must escape single quotes (' ') or backslashes (\) in the procedure definition by doubling them. For details about dollar quoting, see Dollar-Quoted String Constraints in the PostgreSQL documentation.

Examples​

CREATE PROCEDURE insert_data(a integer, b integer)
LANGUAGE SQL
AS $$
INSERT INTO tbl VALUES (a);
INSERT INTO tbl VALUES (b);
$$;

CALL insert_data(1, 2);

Usage notes​

For more information, see CREATE FUNCTION.

To execute a procedure, run CALL.

SQL standard compatibility​

CREATE PROCEDURE in Relyt is partially compatible with CREATE PROCEDURE in the SQL standard. For details about the difference between the two versions, see CREATE FUNCTION.