CREATE PROCEDURE

Synopsis

Use the CREATE PROCEDURE statement to define a new procedure in a database.

Syntax

• Grammar
• Diagram

create_procedure ::= CREATE [ OR REPLACE ] PROCEDURE name ( 
                     [ arg_decl [ , ... ] ] )  
                     { LANGUAGE lang_name
                       | TRANSFORM { FOR TYPE type_name } [ , ... ]
                       | [ EXTERNAL ] SECURITY INVOKER
                       | [ EXTERNAL ] SECURITY DEFINER
                       | SET configuration_parameter 
                         { TO value | = value | FROM CURRENT }
                       | AS 'definition'
                       | AS 'obj_file' 'link_symbol' } [ ... ]

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

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 any existing definition instead.

• 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