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.katz@excoventures.com] Sent: 29 August 2018 22:56 To: Dave Bolt Cc: pgsql-sql@lists.postgresql.org Subject: Re: Stored Procedure to Delete Rows and Return Count
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