CREATE PROCEDURE

Synopsis

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

Syntax

• Grammar
• Diagram

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 run_time_parameter 
                                    { TO value
                                      | = value
                                      | FROM CURRENT }
                                    | RESET run_time_parameter
                                    | RESET ALL
                                    | [ EXTERNAL ] SECURITY 
                                      { INVOKER | DEFINER }

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.

Examples

• Set up an accounts table.

  create schema s;
  
  create table s.accounts (
    id integer primary key,
    name text not null,
    balance decimal(15,2) not null);
  
  insert into s.accounts values (1, 'Jane', 100.00);
  insert into s.accounts values (2, 'John', 50.00);
  
  select * from s.accounts order by 1;
  

   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 s.transfer(from_id in int, to_id in int, amnt in decimal)
    security definer
    set search_path = pg_catalog, pg_temp
    language plpgsql
  as $body$
  begin
    if amnt <= 0.00 then
      raise exception 'The transfer amount must be positive';
    end if;
    if from_id = to_id then
      raise exception 'Sender and receiver cannot be the same';
    end if;
    update s.accounts set balance = balance - amnt where id = from_id;
    update s.accounts set balance = balance + amnt where id = to_id;
  end;
  $body$;
  

• Transfer $20.00 from Jane to John.

  call s.transfer(1, 2, 20.00);
  select * from s.accounts order by 1;
  

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

• Errors will be thrown for unsupported argument values.

  CALL s.transfer(2, 2, 20.00);
  

  ERROR:  Sender and receiver cannot be the same
  

  call s.transfer(1, 2, -20.00);
  

  ERROR:  The transfer amount must be positive
  

create or replace procedure s.transfer(from_id in int, to_id in int, amnt in decimal)
security definer
as $body$
begin
  if amnt <= 0.00 then
    raise exception 'The transfer amount must be positive';
  end if;
  if from_id = to_id then
    raise exception 'Sender and receiver cannot be the same';
  end if;
  update s.accounts set balance = balance - amnt where id = from_id;
  update s.accounts set balance = balance + amnt where id = to_id;
end;
$body$
set search_path = pg_catalog, pg_temp
language plpgsql;

See also

• ALTER PROCEDURE
• DROP PROCEDURE
• CREATE FUNCTION
• ALTER FUNCTION
• DROP FUNCTION