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 invoke a nested procedure.

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' );
...

Parent topic:SQL Commands