Thread: Finding number of rows deleted in a stored procedure
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
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