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
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)
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)
IF p1 is null OR p2 is null THEN
RAISE EXCEPTION 'input cannot be null';
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;
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)
INSERT INTO tb1 VALUES (a, b);
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.