RE: Stored Procedure to Delete Rows and Return Count

Thanks Jonathan, spot on.

Stuck with PG 8.4 at the moment on the target server. Really hope the
production server will be up to date. Couldn't see any indication in the
documentation that there was a difference between 9.anything and 8.4, hence
the confusion.

Meanwhile, your solution is exactly what I need for now.

Dave

From: Jonathan S. Katz [mailto:jonathan(dot)katz(at)excoventures(dot)com]
Sent: 29 August 2018 22:56
To: Dave Bolt
Cc: pgsql-sql(at)lists(dot)postgresql(dot)org
Subject: 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> 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.or
g