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.

A procedure has the following important properties:

• You create it with CREATE PROCEDURE.
• You execute it independently with the CALL statement.
• It does not have a RETURNS clause therefore it does not return a value.
• It can pass values back to the caller through OUT or INOUT parameters.
• Unlike a function, a procedure can perform transaction control, such as COMMIT or ROLLBACK, when you call it in an appropriate transaction context.

If you need to produce a value that you can use in a SQL expression or query, you can use a function. But when you want to perform an operation by calling it explicitly, you use a procedure.

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;Copy

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;Copy

Try it

The following calls the update_safety_stock procedure:

CALL update_safety_stock(1, 100);Copy

Try it

Summary #

• Use the CREATE PROCEDURE statement with the LANGUAGE plpgsql option to create a new procedure.
• Place the procedure statements between BEGIN and END.
• Execute a procedure with the CALL statement.
• A procedure does not return a function value and does not declare a RETURNS clause.

Quiz #