Home > APIs > YSQL > The SQL language > SQL statements >

CREATE PROCEDURE

Synopsis

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

Syntax

Grammar
Diagram

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

arg_decl

procedure_attribute

security_kind

lang_name

implementation_definition

sql_stmt_list

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