Re: Stored Procedure to Delete Rows and Return Count

> On Aug 29, 2018, at 5:50 PM, Jonathan S. Katz <jonathan(dot)katz(at)excoventures(dot)com> wrote:
>
>>
>> On Aug 29, 2018, at 5:46 PM, Dave Bolt <dave(at)davebolt(dot)co(dot)uk <mailto:dave(at)davebolt(dot)co(dot)uk>> wrote:
>>
>> Can someone give me a clue how this can be done and confirm that their solution has been tested so I know it's my end that has the problem, or better yet tell me what I'm doing wrong.
>
> Being able to use a DELETE in a CTE was introduced in PostgreSQL 9.1.

Borrowing from an old post[1] I made this little function:

CREATE FUNCTION delete_stuff (y int)
RETURNS int
AS $$
DECLARE
deleted int;
BEGIN
DELETE FROM a WHERE x = y;
GET DIAGNOSTICS deleted = ROW_COUNT;
RETURN deleted;
END
$$ LANGUAGE plpgsql;

which returns the total # of rows deleted by the above query, which seems like
it could be adapted for your purposes.

Hope this helps,

Jonathan

[1] https://www.postgresql.org/message-id/20071002204841.GD19690%40alvh.no-ip.org