PostgreSQL CREATE PROCEDURE Statement

Summary: in this tutorial, you’ll learn how to use the PostgreSQL CREATE PROCEDURE statement to create a new stored procedure.

Introduction to PostgreSQL CREATE PROCEDURE statement #

A stored procedure is a precompiled set of SQL statements stored and executed on a PostgreSQL database server.

To define a stored procedure, you use the CREATE PROCEUDRE statement with the following syntax:

CREATE OR REPLACE PROCEDURE procedure_name(parameter_list) 
AS
$$
-- body of the stored procedure
$$
LANGUAGE SQL;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• CREATE PROCEDURE: Defines a new stored procedure.
• OR REPLACE: Replaces the existing stored procedure if it exists.
• procedure_name: Specifies the name of the stored procedure.
• parameter_list: A list of parameters of the stored procedure.
• AS: Indicates the start of the stored procedure’s body.
• $$: Encloses the code that makes up the body of the stored procedure.
• LANGUAGE SQL: Specifies that the language of the stored procedure is SQL. You can also use other procedure languages, such as PL/pgSQL.

If you want to execute multiple SQL statements, place them between a BEGIN ATOMIC ... END block:

CREATE OR REPLACE PROCEDURE procedure_name(parameter_list) 
LANGUAGE SQL
BEGIN ATOMIC
    sql_statement1;
    sql_statement2;
END;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax, the BEGIN ATOMIC ... END block ensures that all SQL statements (sql_statement1, sql_staetment2, …) within the block succeed, or none of them do. In other words, it will execute all the statements as a single atomic unit.

Example of a Basic PostgreSQL CREATE PROCEDURE Statement #

The following example uses the CREATE PROCEDURE statement to define a new stored procedure update_safety_stock that updates the safety stock of a product specified by product id:

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

Try it

Calling a PostgreSQL Stored Procedure #

To execute a PostgreSQL stored procedure, use the CALL statement with the following syntax:

CALL procedure_name(arguments);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

For example, to update the safety stock of the product id 1 to 20:

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

Try it

To verify the update, retrieve data from the products table:

SELECT
  product_id,
  safety_stock
FROM
  products
WHERE
  product_id = 1;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 product_id | safety_stock
------------+--------------
          1 |           20Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Creating a Stored Procedure that Executes Multiple Statements #

We’ll use the transactions and inventories tables:

First, define a stored procedure called update_inventory that updates the transaction and inventory:

CREATE OR REPLACE PROCEDURE update_inventory(
    p_id INT,
    w_id INT,
    u_id INT,
    type transaction_type,
    qty INT
)
LANGUAGE SQL
BEGIN ATOMIC
    INSERT INTO transactions (product_id, warehouse_id, user_id, type, quantity, transaction_date)
    VALUES (p_id, w_id, u_id, type, qty, CURRENT_DATE);

    UPDATE inventories
    SET quantity = CASE type 
        WHEN 'receipt' THEN quantity + qty 
        WHEN 'issue' THEN quantity - qty 
        END
    WHERE product_id = p_id AND 
          warehouse_id = w_id;
END;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Then, attempt to issue 10000 products with id 1 from warehouse 1 by calling the update_inventory stored procedure:

CALL update_inventory(1, 1, 1, 'issue', 10000);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

If PostgreSQL issues the following error:

new row for relation "inventories" violates check constraint "inventories_quantity_check"Code language: plaintext (plaintext)

The reason is that the inventory is not sufficient. The UPDATE statement caused a CHECK constraint violation. Since the UPDATE statement failed, both the INSERT and UPDATE statements also failed.

Next, issue 10 products with id 1 from warehouse 1:

CALL update_inventory(1, 1, 1, 'issue', 10);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Finally, verify the inventory by querying data from the transactions and inventories tables:

Querying the transactions table:

SELECT
  type,
  quantity
FROM
  transactions
WHERE
  product_id = 1
  AND warehouse_id = 1
  AND type = 'issue'
  AND transaction_date = CURRENT_DATE;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 type  | quantity
-------+----------
 issue |       10Code language: plaintext (plaintext)

Querying the inventories table:

SELECT
  quantity
FROM
  inventories
WHERE
  product_id = 1
  AND warehouse_id = 1;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 quantity
----------
       90Code language: plaintext (plaintext)

Summary #

• Use the CREATE PROCEDURE statement to create a new stored procedure.

Quiz #