Finding number of rows deleted in a stored procedure

From: "Ross Bagley" <ross(at)rossbagley(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Finding number of rows deleted in a stored procedure
Date: 2007-10-02 20:41:22
Message-ID: 42c4217a0710021341h2c96d963wec46b42eaf58b209@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Newbie to pl/pgsql here.

I'm trying to create a function that cleans up the foreign keys
referring to a particular row (if any exist), then removes the row (if
it exists), and returns the number of rows of br_role that were
deleted (0 or 1).

Newbie stored procedure:

CREATE OR REPLACE FUNCTION delete_role(del_role_pk bigint) RETURNS int AS $$
BEGIN
DELETE FROM br_actor_role_mm WHERE role_fk = del_role_pk;
DELETE FROM br_role_permission_mm WHERE role_fk = del_role_pk;
RETURN DELETE FROM br_role WHERE role_pk = del_role_pk;
END;
$$ LANGUAGE plpgsql;

Here's what hapens when I call it in psql using the "SELECT proc(...);" syntax:

bedrock=> select delete_role(1892);
ERROR: column "delete" does not exist
CONTEXT: SQL statement "SELECT DELETE FROM br_role WHERE role_pk = $1 "
PL/pgSQL function "delete_role" line 4 at return

Hm. That's not quite right. It should be returning the result of the
DELETE query, not the DELETE query itself.

I did come across FOUND, which leads to this:

CREATE OR REPLACE FUNCTION delete_role(del_role_pk bigint) RETURNS int AS $$
BEGIN
DELETE FROM br_actor_role_mm WHERE role_fk = del_role_pk;
DELETE FROM br_role_permission_mm WHERE role_fk = del_role_pk;
DELETE FROM br_role WHERE role_pk = del_role_pk;
IF FOUND THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END;
$$ LANGUAGE plpgsql;

But this technique isn't usable in the next use case, where the number
of deleted rows may be more than one. Seems nasty to have immediate
values in the return statements, too.

Seems like there should be some equivalent to FOUND that stores the
number of updated/deleted rows, but after reading over the docs a
couple of times, I haven't found it.

So, how do I discover the number of rows deleted by a DELETE query?

Thanks in advance,
Ross

--
Ross Bagley
"Security is mostly a superstition. It does not exist in nature...
Life is either a daring adventure or nothing." -- Helen Keller

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alvaro Herrera 2007-10-02 20:48:41 Re: Finding number of rows deleted in a stored procedure
Previous Message Brian Wipf 2007-10-02 20:36:16 PITR and Compressed WALS