PostgreSQL DROP PROCEDURE Statement

Summary: in this tutorial, you’ll learn how to use the PostgreSQL DROP PROCEDURE to remove a stored procedure from a database.

Introduction to PostgreSQL DROP PROCEDURE Statement #

In PostgreSQL, a stored procedure is a named database object that consists of a reusable set of SQL statements. You can use the CREATE PROCEDURE statement to define a stored procedure.

If a stored procedure is no longer in use, you can remove it from the database using the DROP PROCEDURE statement.

Here’s the syntax of the DROP PROCEDURE statement:

DROP PROCEDURE [IF EXISTS] procedure_name (parameter_list);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• First, specify the stored procedure name you want to remove after the DROP PROCEDURE keywords. Use the IF EXISTS clause to prevent an error if the stored procedure does not exist.
• Second, use a list of parameters to identify the stored procedure if the database has multiple procedures with the same name.

PostgreSQL will raise an error if you attempt to drop a stored procedure with dependent objects. To remove the stored procedure as well as the objects that depend on it, you use the CASCADE option:

DROP PROCEDURE [IF EXISTS] procedure_name (parameter_list)
CASCADE;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

By default, the DROP PROCEDURE statement uses the RESTRICT option, which refuses to drop the procedure if it has any objects depending on it:

DROP PROCEDURE [IF EXISTS] procedure_name (parameter_list)
RESTRICT;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

You can remove multiple stored procedures using a single DROP PROCEDURE statement:

DROP PROCEDURE 
     procedure_name1(parameter_list),
     procedure_name2(parameter_list);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax, you provide a comma-separated list of stored procedure names you want to remove after the DROP PROCEDURE keywords.

PostgreSQL does not track the dependencies between stored procedures. If stored procedure A calls stored procedure B, you can drop stored procedure B without any issue. However, if you execute stored procedure A, you’ll get an error.

PostgreSQL DROP PROCEDURE Statement Examples #

Let’s explore examples of using the DROP PROCEDURE statement.

Creating Stored Procedures #

First, create a stored procedure that adds a new brand to the brands table:

CREATE OR REPLACE PROCEDURE add_brand(
     p_name VARCHAR(255)
)
AS 
$$
    INSERT INTO brands (brand_name)
    VALUES (p_name);
$$
LANGUAGE SQL;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Second, create a stored procedure that increases the inventory:

CREATE OR REPLACE PROCEDURE increase_inventory(
    p_product_id INT,
    p_warehouse_id INT,
    p_quantity INT
)
AS 
$$
    UPDATE inventories
    SET quantity = quantity + p_quantity 
    WHERE product_id = p_product_id AND warehouse_id = p_warehouse_id;
$$
LANGUAGE SQL;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Third, define a stored procedure that records a transaction that receives products into the warehouse and updates the inventory accordingly:

CREATE OR REPLACE PROCEDURE receive_product(
    p_product_id INT,
    p_warehouse_id INT,
    p_user_id INT,
    p_quantity INT
)
AS 
$$
    INSERT INTO transactions (product_id, warehouse_id, user_id, type, quantity, transaction_date)
    VALUES (p_product_id, p_warehouse_id, p_user_id, 'receipt', p_quantity, CURRENT_DATE);

    CALL increase_inventory(p_product_id, p_warehouse_id, p_quantity);
$$
LANGUAGE SQL;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

The receive_product stored procedure calls the increase_inventory stored procedure.

Dropping a Stored Procedure #

The following example uses the DROP PROCEDURE statement to drop the stored procedure add_brand:

DROP PROCEDURE IF EXISTS add_brand;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Since the inventory database has one add_brand stored procedure, we do not use paramter list after the stored procedure name.

Dropping a Stored Procedure that Calls Another #

First, drop the increase_inventory stored procedure:

DROP PROCEDURE increase_inventory;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

It works as expected.

Second, call the receive_product stored procedure:

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

Try it

It returns the following error:

ERROR:  procedure increase_inventory(integer, integer, integer) does not existCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Since we stored procedure increase_inventory does not exist anymore, the receive_product stored procedure failed.

Third, drop the stored procedure receive_product:

DROP PROCEDURE receive_product;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

• Use the DROP PROCEDURE statement to remove a stored procedure.

Quiz #