PL/pgSQL Procedures

Summary: In this tutorial, you’ll learn how to create a procedure using PL/pgSQL in PostgreSQL.

Creating PL/pgSQL Procedures #

A procedure is a reusable piece of code stored in PostgreSQL database server, which performs a specific task.

Besides user-defined functions, procedures offer a way to encapsulate complex business logic centrally at the database layer.

PostgreSQL allows you to define procedures using various languages, including SQL and PL/pgSQL.

To create a procedure using PL/pgSQL, you use the CREATE PROCEDURE statement with the following syntax:

CREATE OR REPLACE PROCEDURE procedure_name(parameters)
AS
$$
DECLARE
    -- declaration
BEGIN
    -- body
END;
$$
LANGUAGE plpgsql;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• Place the PL/pgSQL code between a dollar-quoted string literal ($$).
• Use plpgsql as the language in the LANGUAGE clause.

Creating PL/pgSQL procedure example #

The following statement creates a procedure called update_safety_stock using PL/pgSQL, which updates the safety stock of a product specified by an id from the products table:

CREATE OR REPLACE PROCEDURE update_safety_stock(
    id INT,
    new_safety_stock INT
)
AS
$$
BEGIN
    UPDATE products
    SET safety_stock = new_safety_stock
    WHERE product_id = id;
END;
$$
LANGUAGE plpgsql;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

The following calls the update_safety_stock procedure:

CALL update_safety_stock(1, 100);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Summary #

• Use the CREATE PROCEDURE statement with the LANGUAGE plpgsql option to create a new procedure.

Quiz #