CREATE PROCEDURE

Synopsis

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

Syntax

create_procedure ::= CREATE [ OR REPLACE ] PROCEDURE name ( 
                     [ arg_decl [ , ... ] ] ) procedure_attribute 
                     [ ... ]

arg_decl ::= [ argmode ] [ argname ] argtype 
             [ { DEFAULT | = } expression ]

procedure_attribute ::= TRANSFORM { FOR TYPE type_name } [ , ... ]
                        | SET configuration_parameter 
                          { TO value | = value | FROM CURRENT }
                        | [ EXTERNAL ] SECURITY security_kind
                        | LANGUAGE lang_name
                        | AS implementation_definition

security_kind ::= INVOKER | DEFINER

lang_name ::= SQL | PLPGSQL | C

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

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

create_procedure

CREATEORREPLACEPROCEDUREname(,arg_decl)procedure_attribute

arg_decl

argmodeargnameargtypeDEFAULT=expression

procedure_attribute

TRANSFORM,FORTYPEtype_nameSETconfiguration_parameterTOvalue=valueFROMCURRENTEXTERNALSECURITYsecurity_kindLANGUAGElang_nameASimplementation_definition

security_kind

INVOKERDEFINER

lang_name

SQLPLPGSQLC

implementation_definition

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

sql_stmt_list

sql_stmt;sql_stmt;

Semantics

  • If a procedure with the given name and argument types already exists then CREATE PROCEDURE will throw an error unless the CREATE OR REPLACE PROCEDURE version is used. In that case it will replace the existing definition.

  • The languages supported by default are sql, plpgsql and C.

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