CREATE PROCEDURE

Create a stored procedure, using the plpgsql language. For details about plpgsql, see the PostgreSQL 9.5 documentation.

CREATE [ OR REPLACE ] PROCEDURE name
( [ [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] ) 
 [ RETURNS rettype
AS $$
[ DECLARE alias [ aliastype ] [ ALIAS FOR $n ]; [ ... ] ]
BEGIN
  [ body ];
END;
$$ LANGUAGE plpgsql;
PROCEDURE name
You can create multiple procedures with the same name if they have different signatures (arguments and data types). Use empty parentheses after the name if the procedure has no arguments.
Note: Although superusers are allowed to create stored procedures in the sys schema, this practice is not recommended. All users should create stored procedures in public or a user-defined schema.
[ argname ] argtype ]
The name (or alias) for an argument is optional. Its data type is required. Stored procedures may have arguments that use any of the supported Yellowbrick data types.
DEFAULT | =
Define a value for the argument that will be supplied automatically when the procedure is called without a value for that argument. If an argument has a default value, all of the arguments that follow must have default values.
RETURNS rettype
Return a value or a set of values from the procedure:
  • data_type: specify the return data type for a return value
  • SETOF table: return a set of values instead of a single value
    Note: A procedure that returns a set must be called with a SELECT statement, and the query must not contain table references. For example:
    select * from proc_set() order by 1;
DECLARE
You can declare aliases explicitly here, or you can specify them in the argument list after the procedure name. You can use the ALIAS FOR $n syntax if you do not specify a data type for the alias. For example:
declare day alias for $1;
BEGIN
For detailed information about the syntax that you can use in the body of a plpgsql stored procedure, see the PostgreSQL 9.5 documentation. To implement transaction commit and rollback support, use the BEGIN...EXCEPTION construct inside the body. The entire outer BEGIN block must end with a semicolon.
END
The END keyword must be followed by a semicolon.
LANGUAGE plpgsql
Yellowbrick stored procedures support the plpgsql language only.

Transaction Control Within PL/pgSQL Procedures

Within procedures executed with the CALL command, you can end transactions by using the COMMIT and ROLLBACK commands. A new transaction is started automatically after a transaction is ended with these commands; there is no separate START TRANSACTION command. (Note that BEGIN and END have different meanings in PL/pgSQL.)

For example:
CREATE PROCEDURE transaction_test1() 
AS $$
BEGIN
    FOR i IN 0..9 LOOP
        INSERT INTO test1 (a) VALUES (i);
        IF i % 2 = 0 THEN
            COMMIT;
        ELSE
            ROLLBACK;
        END IF;
    END LOOP;
END
$$ LANGUAGE plpgsql; 
CREATE PROCEDURE
premdb=# create table test1(a int);
CREATE TABLE
premdb=# CALL transaction_test1();
CALL

Examples

For example, create a procedure that runs an INSERT:
premdb=# create procedure addteam(name varchar(30)) as 
$$ begin 
insert into team(name) values(name); 
end; 
$$ language plpgsql;
CREATE PROCEDURE
Create (or replace) a similar INSERT procedure that takes two parameters and declares aliases:
premdb=# create or replace procedure insert_hometeam(integer,varchar(30))
as $$
declare 
   htid alias for $1;
   name alias for $2;
begin
   insert into hometeam(htid, name)
   values ($1, $2);
end;
$$ language plpgsql;
CREATE PROCEDURE
Create a similar procedure but provide default values for both arguments:
premdb=# create or replace procedure 
insert_hometeam(integer=1,varchar(30)='Team 1')
...
Create a procedure that runs an UPDATE:
premdb=# create or replace procedure update_match
(timestamp, char(3))
as $$
declare 
    day alias for $1;
    score alias for $2;
begin
   update match
   set ftscore=$2
   where matchday>$1;
end;
$$ language plpgsql;
CREATE PROCEDURE
Here is an example CALL command that runs the update_match procedure:
premdb=# call update_match('2015-01-01','0-0');
CALL
Create and run a simple procedure that returns its own input value:
premdb=# create procedure sp2(val int4) returns int as $$
begin
return val;
end$$ language plpgsql;
CREATE PROCEDURE
premdb=# call sp2(20);
CALL
premdb=# select sp2(20);
 sp2 
-----
  20
(1 row)
Create and run a procedure that selects all rows from the team table:
premdb=# create procedure sp_team_table() returns setof team as $$
declare
rec record;
begin
for rec in EXECUTE 'select * from team order by 1,2,3,4,5' loop
return next rec;
end loop;
end
$$ language plpgsql;
CREATE PROCEDURE

premdb=# select name, stadium, capacity from sp_team_table() 
where capacity <25000 and name is not null 
order by 3;
        name         |     stadium      | capacity 
---------------------+------------------+----------
 Bournemouth         | Vitality Stadium |    11464
 Oldham Athletic     | Boundary Park    |    13309
 Swindon Town        | County Ground    |    15728
 Blackpool           | Bloomfield Road  |    17338
 Queens Park Rangers | Loftus Road      |    18439
 Swansea City        | Liberty Stadium  |    20520
 Portsmouth          | Fratton Park     |    21100
 Watford             | Vicarage Road    |    21577
 Burnley             | Turf Moor        |    22546
 Barnsley            | Oakwell Stadium  |    23009
 Reading             | Madejski Stadium |    24161
(11 rows)
Use the \dfp or \dfp+ command to return information about the stored procedures in the database. For example:
premdb=# \dfp
                         List of functions and stored procedures
 Schema |       Name       | Result data type |  Argument data types   |       Type       
--------+------------------+------------------+------------------------+------------------
 public | addteam          | void             | name character varying | stored procedure
 public | sp2              | integer          | val integer            | stored procedure
 public | sp_team_table    | SETOF team       |                        | stored procedure
 ...

Usage Notes

  • When a stored procedure with a return value is executed via a CALL statement, the return value is not displayed. If the same procedure is invoked via a SELECT statement, it returns the value.
  • When a procedure with no return value is executed via a SELECT statement, it returns NULL.
  • A procedure that returns a set must be called with a simple SELECT statement, and the query must not contain a FROM clause with table references.
  • Yellowbrick does not support trigger procedures.
  • Yellowbrick does not support collation of plpgsql variables.
  • Integer data types are not implicitly downcast when they are passed as parameters to stored procedures. For example, a constant value of 2000 is interpreted as an INTEGER data type, not as a SMALLINT. Therefore, the following attempt to call a stored procedure that was created with a SMALLINT argument fails with an error. When the constant is explicitly cast to SMALLINT, the CALL command succeeds.
    premdb=# call insert_hometeam(2000);
    ERROR:  stored procedure insert_hometeam(integer) does not exist
    HINT:  No stored procedure matches the given name and argument types.
    premdb=# call insert_hometeam(2000::smallint);
    CALL
    Data types are implicitly upcast where necessary; this behavior only applies to values that require downcasting.

Invocation of Stored Procedures via CALL and SELECT

The following table shows the behavior for different kinds of stored procedures, depending on their structure, return type, and how they are invoked: via CALL or SELECT. In most cases, you cannot CALL a nested procedure (a stored procedure within another stored procedure), but you can use a simple SELECT to invoked a nested procedure.

Procedure Returns Invoked with CALL Invoked with SELECT
Not nested Nothing (VOID) Allowed Allowed
Scalar value Allowed but result discarded (not displayed) Allowed and result returned
A set (SETOF) Disallowed, returns an error Allowed
Nested with no arguments or constant arguments Nothing (VOID) Allowed Allowed
Scalar value Disallowed, returns an error Allowed
A set (SETOF) Disallowed, returns an error Allowed
Nested with variable arguments Nothing (VOID) Disallowed, returns an error Allowed
Scalar value Disallowed, returns an error Allowed
A set (SETOF) Disallowed, returns an error Allowed
For example, the procedure below (public.raise_v_p) fails to execute when it is nested and causes its outer procedure to fail. Attempts to SELECT or CALL public.echo_n_raise_i_p both fail because you cannot CALL a nested procedure that takes a variable as an argument.
CREATE OR REPLACE PROCEDURE public.raise_v_p( _arg_v varchar )
AS 
$$
DECLARE
   tmp integer := 0;
BEGIN
   RAISE INFO '%', _arg_v;
END;   
$$ 
LANGUAGE plpgsql ;

CREATE OR REPLACE PROCEDURE public.echo_n_raise_i_p( _arg_i int )
RETURNS text AS 
$$
BEGIN
  CALL public.raise_v_p( _arg_i );
  RETURN _arg_i::text;
END;   
$$ 
LANGUAGE plpgsql ;

premdb=# SELECT * FROM public.echo_n_raise_i_p(1);
ERROR:  column "_arg_i" does not exist
CONTEXT:  SQL statement "CALL public.raise_v_p( _arg_i )"
PL/pgSQL function echo_n_raise_i_p(integer) line 3 at SQL statement

premdb=# CALL public.echo_n_raise_i_p(1);
ERROR:  column "_arg_i" does not exist
CONTEXT:  SQL statement "CALL public.raise_v_p( _arg_i )"
PL/pgSQL function echo_n_raise_i_p(integer) line 3 at SQL statement
However, the nested procedure will run if you replace the variable with a constant:
...
CALL public.raise_v_p( '10' );
...