Re: Finding number of rows deleted in a stored procedure

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