Search Postgresql Archives

Finding number of rows deleted in a stored procedure

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org/

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux