PostgreSQL Create Procedure Examples

Version 11 of PostgreSQL introduced the ability to create PostgreSQL stored procedures using the CREATE PROCEDURE statement. Versions of PostgreSQL prior to version 11 supported creating stored functions, but not stored procedures. If using a version of PostgreSQL prior to version 11, see the following page that has examples for creating PostgreSQL functions:

PostgreSQL Create Function Examples

PostgreSQL procedures can be created by connecting to a PostgreSQL database and executing a create procedure statement. Below is the syntax for the create procedure statement:

CREATE [OR REPLACE] PROCEDURE procedure_name(parameter_list)
LANGUAGE language_name
AS $procedure$
    --procedure body;
$procedure$;

The variables that need filled in by the user are the following:

1. procedure_name: Enter the name of the procedure here, for example, update_salary

2. parameter_list: The parameter list contains three things. The first is the type of the parameter. The currently supported types are IN and INOUT. The second is the name of the parameter, and the third is the data type of the parameter. If the parameter type is omitted, an IN parameter type is assumed. Here is an example parameter list:

(f1 integer, INOUT f2 character varying)

3. language_name: Built-in languages supported by PostgreSQL are sql and plpgsql. If the Python language pack is installed in the database, the Python language is also supported using the plpythonu language name.

4. procedure_body: The procedure body contains the commands to execute. The commands are written in the language specified by the language_name, for example, SQL.

The following example procedure named test_procedure has two parameters. The first is an IN parameter and the second is an INOUT parameter. The procedure checks to see if the parameters passed in are valid. If they are not, an exception is raised. The procedure then creates a temporary table and inserts data into the table based on the values of the passed in parameters. This procedure is written using the PostgreSQL procedurual language: plpgsql

To call the procedure below, the call syntax can be used. Here is an example:

call public.test_procedure(2, '3');

Since the p2 variable is an INOUT parameter, its value will be populated after the procedure is called. Tools like RazorSQL will display the value of p2 in the query results section after calling the procedure.

CREATE OR REPLACE PROCEDURE public.test_procedure(p1 integer,  INOUT p2 character varying)
 LANGUAGE plpgsql
AS $procedure$
DECLARE
  loop_var int;
BEGIN
  IF p1 is null OR p2 is null THEN
    RAISE EXCEPTION 'input cannot be null';
  END IF;
  DROP TABLE if exists test_table;
  CREATE TEMP TABLE test_table(a int, b varchar);
    FOR loop_var IN 1..p1 LOOP
        insert into test_table values (loop_var, p2);
        p2 := p2 || '+' || p2;
    END LOOP;
END;
$procedure$

Below is another example PostgreSQL stored procedure. This procedure is written in SQL. This procedure simply takes the input parameters and inserts them into a table.

CREATE OR REPLACE PROCEDURE public.insert_data(a integer, b integer)
 LANGUAGE sql
AS $procedure$
INSERT INTO tb1 VALUES (a, b);
$procedure$

If you are looking for a tool that provides the capability to edit, create, call, and view PostgreSQL stored procedures, please check out RazorSQL. It has a free, full-featured 30-day trial and can be downloaded from the following: RazorSQL Download.