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:[email protected]] 
Sent: 29 August 2018 22:56
To: Dave Bolt
Cc: [email protected]
Subject: Re: Stored Procedure to Delete Rows and Return Count

 

 

On Aug 29, 2018, at 5:50 PM, Jonathan S. Katz
<[email protected]> wrote:

 


On Aug 29, 2018, at 5:46 PM, Dave Bolt <[email protected]> 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