PL/pgSQL DROP PROCEDURE

Master DROP PROCEDURE — argument-list disambiguation for overloads, CASCADE vs RESTRICT semantics, IF EXISTS for idempotent drops, and multi-procedure drop syntax. Critical fixes: original INSERT used semicolons between tuples instead of commas, procedures hard-coded literal values instead of using parameters, and the multi-drop tried to drop a procedure that was never created.

The DROP PROCEDURE statement deletes a stored procedure from the database. It mirrors the structure of DROP TABLE, DROP FUNCTION, etc., with two specific concerns to think about:

• Argument list disambiguation — multiple procedures can share a name with different argument types (overloading). When dropping, you may need to specify which one.
• Dependent objects — other procedures, views, or triggers may depend on the one you're dropping. CASCADE drops them too; RESTRICT (default) refuses if any exist.

To drop multiple procedures in one statement, separate them with commas:

Create an actors table and a few procedures we can drop:

DROP TABLE IF EXISTS actors;

CREATE TABLE actors (
    actor_id    INT PRIMARY KEY,
    first_name  VARCHAR(50) NOT NULL,
    last_name   VARCHAR(50) NOT NULL,
    last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO actors (actor_id, first_name, last_name) VALUES
    (10, 'Leonardo',  'DiCaprio'),
    (20, 'Jennifer',  'Lawrence'),
    (30, 'Tom',       'Hanks'),
    (40, 'Meryl',     'Streep'),
    (50, 'Denzel',    'Washington');

Now create two procedures with the same name but different arguments — classic overloading:

-- Version 1: takes first and last name separately
CREATE OR REPLACE PROCEDURE insert_actor(
    p_actor_id INT,
    fname      VARCHAR,
    lname      VARCHAR
)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO actors (actor_id, first_name, last_name)
    VALUES (p_actor_id, fname, lname);
END;
$$;

-- Version 2: takes a full name and splits it
CREATE OR REPLACE PROCEDURE insert_actor(
    p_actor_id INT,
    full_name  VARCHAR
)
LANGUAGE plpgsql
AS $$
DECLARE
    fname VARCHAR;
    lname VARCHAR;
BEGIN
    SELECT split_part(full_name, ' ', 1),
           split_part(full_name, ' ', 2)
    INTO   fname, lname;

    INSERT INTO actors (actor_id, first_name, last_name)
    VALUES (p_actor_id, fname, lname);
END;
$$;

-- Plus an unrelated update procedure
CREATE OR REPLACE PROCEDURE update_actor(
    p_actor_id INT,
    fname      VARCHAR,
    lname      VARCHAR
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE actors
    SET    first_name = fname,
           last_name  = lname
    WHERE  actor_id = p_actor_id;
END;
$$;

Try dropping insert_actor without specifying which one. With two overloaded versions, PostgreSQL doesn't know which one you mean:

DROP PROCEDURE insert_actor;

Specify which version by listing its argument types:

-- Drop only the (INT, VARCHAR) version
DROP PROCEDURE insert_actor(INT, VARCHAR);

Now only the three-argument insert_actor(INT, VARCHAR, VARCHAR) remains. Since it's now unique, you could drop it without the argument list:

DROP PROCEDURE insert_actor;
-- equivalent to:
-- DROP PROCEDURE insert_actor(INT, VARCHAR, VARCHAR);

In deployment scripts, you usually want to drop-then-recreate without erroring on the first run when the procedure doesn't exist yet:

DROP PROCEDURE IF EXISTS update_actor(INT, VARCHAR, VARCHAR);
-- (then immediately re-create it)

First call drops it. Second call sees it doesn't exist and emits a NOTICE rather than an error — the transaction continues. This is the right pattern for the start of a deployment script.

-- Recreate them first so we have something to drop
CREATE OR REPLACE PROCEDURE insert_actor(p_actor_id INT, fname VARCHAR, lname VARCHAR)
LANGUAGE plpgsql AS $$
BEGIN
    INSERT INTO actors (actor_id, first_name, last_name)
    VALUES (p_actor_id, fname, lname);
END;
$$;

CREATE OR REPLACE PROCEDURE update_actor(p_actor_id INT, fname VARCHAR, lname VARCHAR)
LANGUAGE plpgsql AS $$
BEGIN
    UPDATE actors SET first_name = fname, last_name = lname WHERE actor_id = p_actor_id;
END;
$$;

-- Drop both at once
DROP PROCEDURE
    insert_actor,
    update_actor;

RESTRICT (the default) refuses to drop a procedure if anything depends on it — another procedure that calls it, a trigger that uses it, etc. CASCADE drops the dependents too:

-- Drops the procedure AND any dependent objects
DROP PROCEDURE insert_actor(INT, VARCHAR, VARCHAR) CASCADE;

1. Use IF EXISTS in deployment scripts. Prevents errors when the procedure doesn't exist on first deploy.
2. Specify the argument list when dropping. Even if the name is currently unique, future overloading would silently make your drop ambiguous and fail.
3. Default to RESTRICT. Reach for CASCADE only when you've reviewed the dependency list and know what's about to be dropped.
4. Write DROP PROCEDURE IF EXISTS ... before CREATE OR REPLACE in deployments — handles signature changes that CREATE OR REPLACE alone can't (you can't replace a 2-arg procedure with a 3-arg version of the same name).
5. Don't drop multiple procedures in one statement unless they're related and you want all-or-nothing semantics. A failure on one cancels the whole list (since DDL runs in a transaction).
6. Document procedure dependencies. If procedure A calls procedure B, leave a comment on A noting that fact — saves the next maintainer from confusing CASCADE-induced surprises.

• DROP PROCEDURE [IF EXISTS] name [(args)] [CASCADE | RESTRICT] removes a procedure from the database.
• If the procedure name is ambiguous (overloaded), you must specify the argument list. Otherwise it's optional.
• Use IF EXISTS in deployment scripts so the first deploy doesn't error on a missing procedure.
• Default to RESTRICT. Reach for CASCADE deliberately, knowing what dependent objects will be dropped.
• You can drop several procedures in one statement with comma-separation — but the whole statement fails if any one of them is missing or ambiguous.