Finding number of rows deleted in a stored procedure

Поиск
Список
Период
Сортировка
От Ross Bagley
Тема Finding number of rows deleted in a stored procedure
Дата
Msg-id 42c4217a0710021341h2c96d963wec46b42eaf58b209@mail.gmail.com
обсуждение исходный текст
Ответы Re: Finding number of rows deleted in a stored procedure  (Alvaro Herrera <alvherre@commandprompt.com>)
Re: Finding number of rows deleted in a stored procedure  ("Joshua D. Drake" <jd@commandprompt.com>)
Список 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

В списке pgsql-general по дате отправления:

Предыдущее
От: "paul rivers"
Дата:
Сообщение: Re: Partitioned table limitation
Следующее
От: Alvaro Herrera
Дата:
Сообщение: Re: Finding number of rows deleted in a stored procedure