Re: Stored Procedure to Delete Rows and Return Count

"Jonathan S. Katz" <[email protected]> Wed, 29 Aug 2018 17:56:14 -0400

Newsgroups	gmane.comp.db.postgresql.sql
Message-ID	<[email protected]>

> 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] <mailto:[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.org

signature.asc (application/pgp-signature, 833 B)