How to Create/Use/Delete Stored Procedures in PostgreSQL

PostgreSQL’s stored functions allow you to place code in reusable containers, allowing you to write less code. This article explains how to use this convenient and powerful tool.

This article will show you how to create, use, and manage Stored Procedures in PostgreSQL, with easy to follow examples.

Stored procedures are one of the most useful and powerful PostgreSQL features. Stored procedures encapsulate multiple SQL statements into a single reusable code block. This lets you simplify your code, and provide a reliable way to repeat the same tasks over and over, such as moving data between tables or deleting old records.

Note that stored procedures are similar to, but not the same as, stored functions.

How to Create Stored Procedures

Stored procedures are created in PostgreSQL using the CREATE PROCEDURE statement, which has the following syntax:

CREATE PROCEDURE procedure_name (parameter_1 data_type, parameter_2 data_type)
LANGUAGE language
AS $$
-- Your code goes here
$$;

Note that:

• procedure_name is the name of the function you are creating. It must be unique.
• multiple parameters, each with a type, can be defined. If accepting more than one parameter, separate them with a comma.
• The language the code in the procedure is written in must be specified. This will usually be SQL.
• The AS statement will contain your procedure code. Note the $$ – these are dollar quotes to encapsulate multiple lines of code.
• Unlike functions, there is no return value! No data will be returned by procedures.

Below, an example procedure is created that deletes records from an employee table older than a given number of days:

CREATE PROCEDURE delete_old_employees(days INTEGER)
LANGUAGE SQL
AS $$
    DELETE from employees_table
    WHERE created_at < now() -  days * interval '1 days';
$$;

Procedures are perfect for database maintenance tasks such as removing old data, or inserting bulk data. Repeated tasks could be scheduled using cron, or called from a remote script.

How to Use Stored Procedures in PostgreSQL

Stored procedures exist to run code for repeating tasks. Thus, they do not have a return value, and are called using the CALL statement:

CALL procedure_name(parameter_value_1, parameter_value_2);

How to Rename a Stored Procedure in PostgreSQL

The ALTER PROCEDURE statement is used to rename a stored procedure:

ALTER PROCEDURE procedure_name(arguments)
RENAME TO new_procedure_name;

Note that the arguments must be supplied for the original stored procedure in the first line.

How to Delete a Stored Procedure in PostgreSQL

Finally, if you no longer need a stored procedure and want to delete it entirely, use the DROP PROCEDURE statement:

DROP PROCEDURE procedure_name(arguments);