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

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

• CREATE FUNCTION
• CREATE TRIGGER
• DROP PROCEDURE