Finding number of rows deleted in a stored procedure

Ross Bagley wrote:

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;

Right. Use GET DIAGNOSTICS foo = ROW_COUNT

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Ross Bagley wrote:

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).

Maybe I am missing something, but wouldn't ON DELETE CASCADE do what you
need?

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.

You don't return a query... you return the result of the query.

Take a look at:

http://www.postgresql.org/docs/8.2/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-NORESULT

Section 37.6.3. Executing a Query with a Single-Row Result

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

- --

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFHAq9zATb/zqfZUUQRAmiWAJ9SBttz97WqNPcOKCRX8PktneqaGQCfbS09
C6a02LkLzWgko9JuzjzGQaM=
=6F9a
-----END PGP SIGNATURE-----

On 10/2/07, Alvaro Herrera <alvherre@commandprompt.com> wrote:

Right. Use GET DIAGNOSTICS foo = ROW_COUNT

Works great! Thank you.

Ross

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

In this simplified case, given an open SQL connection, you're correct.
That would simplify this query, and I'm a little embarrassed not to
have seen that (obexcuse: I've been spending too much time in
Java-land lately). There is more to the function than I included in
my question, so it does need to be a function and can't be straight
SQL.

Thanks,
Ross

On 10/2/07, Joshua D. Drake <jd@commandprompt.com> wrote:

Maybe I am missing something, but wouldn't ON DELETE CASCADE do what you
need?

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