CREATE PROCEDURE

Synopsis

Use the CREATE PROCEDURE statement to create a procedure in a database.

Syntax

create_procedure ::= CREATE [ OR REPLACE ] PROCEDURE subprogram_name ( 
                     [ arg_decl_with_dflt [ , ... ] ] )  
                     { unalterable_proc_attribute
                       | alterable_fn_and_proc_attribute } [ ... ]

arg_decl_with_dflt ::= arg_decl [ { DEFAULT | = } expression ]

arg_decl ::= [ arg_name ] [ arg_mode ] arg_type

subprogram_signature ::= arg_decl [ , ... ]

unalterable_proc_attribute ::= [ LANGUAGE lang_name
                                 | AS implementation_definition ]

lang_name ::= SQL | PLPGSQL | C

implementation_definition ::= ' sql_stmt_list '
                              | ' plpgsql_block_stmt '
                              | ' obj_file ' [ , ' link_symbol ' ]

sql_stmt_list ::= sql_stmt ; [ sql_stmt ... ]

alterable_fn_and_proc_attribute ::= SET configuration_parameter 
                                    { TO value
                                      | = value
                                      | FROM CURRENT }
                                    | RESET configuration_parameter
                                    | RESET ALL
                                    | [ EXTERNAL ] SECURITY 
                                      { INVOKER | DEFINER }

create_procedure

CREATEORREPLACEPROCEDUREsubprogram_name(,arg_decl_with_dflt)unalterable_proc_attributealterable_fn_and_proc_attribute

arg_decl_with_dflt

arg_declDEFAULT=expression

arg_decl

arg_namearg_modearg_type

subprogram_signature

,arg_decl

unalterable_proc_attribute

LANGUAGElang_nameASimplementation_definition

lang_name

SQLPLPGSQLC

implementation_definition

'sql_stmt_list''plpgsql_block_stmt''obj_file','link_symbol'

sql_stmt_list

sql_stmt;sql_stmt;

alterable_fn_and_proc_attribute

SETconfiguration_parameterTOvalue=valueFROMCURRENTRESETconfiguration_parameterRESETALLEXTERNALSECURITYINVOKERDEFINER

'create procedure' and the 'subprogram_signature' rule.

When you write a CREATE PROCEDURE statement, you will already have decided what formal arguments it will have—i.e. for each, what will be its name, mode, data type, and optionally its default value. When, later, you alter or drop a procedure, you must identify it. You do this, in ALTER PROCEDURE and DROP PROCEDURE, typically by specifying just its subprogram_call_signature. You are allowed to use the full subprogram_signature. But this is unconventional. Notice that the subprogram_signature does not include the optional specification of default values; and you cannot mention these when you alter or drop a procedure. The distinction between the subprogram_signature and the subprogram_call_signature is discussed carefully in the section Subprogram overloading.

Semantics

  • The meanings of the various procedure attributes are explained in the section Subprogram attributes.

  • A procedure, like other schema objects such as a table, inevitably has an owner. You cannot specify the owner explicitly when a procedure is created. Rather, it's defined implicitly as what the current_user built-in function returns when it's invoked in the session that creates the procedure. This user must have the usage privilege on the procedure's schema and its argument data types. You (optionally) specify the procedure's schema and (mandatorily) its name within its schema as the argument of the subprogram_name rule.

  • If a procedure with the given name, schema, and argument types already exists then CREATE PROCEDURE will draw an error unless the CREATE OR REPLACE PROCEDURE variant is used.

  • Procedures with different subprogram_call_signatures can share the same subprogram_name. (The same holds for functions.) See section Subprogram overloading.

  • CREATE OR REPLACE PROCEDURE doesn't change permissions that have been granted on an existing procedure. To use this statement, the current user must own the procedure, or be a member of the role that owns it.

  • In contrast, if you drop and then recreate a procedure, the new procedure is not the same entity as the old one. So you will have to drop existing objects that depend upon the old procedure. (Dropping the procedure CASCADE achieves this.) Alternatively, ALTER PROCEDURE can be used to change most of the auxiliary attributes of an existing procedure.

  • The languages supported by default are SQL, PLPGSQL and C.

'Create procedure' grants 'execute' to 'public'.

Execute is granted automatically to public when you create a new procedure. This is very unlikely to be want you want—and so this behavior presents a disguised security risk. Yugabyte recommends that your standard practice be to revoke this privilege immediately after creating a procedure.

You cannot set the 'depends on extension' attribute with 'create procedure'.

A procedure's depends on extension attribute cannot be set using CREATE [OR REPLACE] PROCEDURE. You must use ALTER PROCEDURE to set it.

Examples

  • Set up an accounts table.

    CREATE TABLE accounts (
  id integer PRIMARY KEY,
  name text NOT NULL,
  balance decimal(15,2) NOT NULL
);

INSERT INTO accounts VALUES (1, 'Jane', 100.00);
INSERT INTO accounts VALUES (2, 'John', 50.00);

SELECT * from accounts;

     id | name | balance
----+------+---------
  1 | Jane |  100.00
  2 | John |   50.00
(2 rows)

  • Define a transfer procedure to transfer money from one account to another.

    CREATE OR REPLACE PROCEDURE transfer(integer, integer, decimal)
LANGUAGE plpgsql
AS $$
BEGIN
  IF $3 <= 0.00 then RAISE EXCEPTION 'Can only transfer positive amounts'; END IF;
  IF $1 = $2 then RAISE EXCEPTION 'Sender and receiver cannot be the same'; END IF;
  UPDATE accounts SET balance = balance - $3 WHERE id = $1;
  UPDATE accounts SET balance = balance + $3 WHERE id = $2;
  COMMIT;
END;
$$;

  • Transfer $20.00 from Jane to John.

    CALL transfer(1, 2, 20.00);
SELECT * from accounts;

     id | name | balance
----+------+---------
  1 | Jane |   80.00
  2 | John |   70.00
(2 rows)

  • Errors will be thrown for unsupported argument values.

    CALL transfer(2, 2, 20.00);

    ERROR:  Sender and receiver cannot be the same
CONTEXT:  PL/pgSQL function transfer(integer,integer,numeric) line 4 at RAISE

    yugabyte=# CALL transfer(1, 2, -20.00);

    ERROR:  Can only transfer positive amounts
CONTEXT:  PL/pgSQL function transfer(integer,integer,numeric) line 3 at RAISE

See also