postgresqlplpgsql

How to assign affected row count to a variable?

I'm struggling with a opstgres syntax.

I have a variable and I can get number of affected rows using returning clause

DECLARE deletedRowCount integer;
WITH deleted as 
(
    DELETE FROM MyTable "t"
    USING tmp_closed_positions
    WHERE condition
    RETURNING "t"."Id"
)
SELECT COUNT(*) FROM deleted;

How do I assign the resulting count to the variable?

Solution

  • You can simply use the PLPGSQL:

    DO $$
DECLARE deletedRowCount integer;
BEGIN
    WITH deleted as 
    (
        DELETE FROM MyTable "t"
        USING tmp_closed_positions
        WHERE condition
        RETURNING "t"."Id"
    )
    SELECT COUNT(*) FROM deleted  INTO deletedRowCount;

    RAISE NOTICE 'Value: %', deletedRowCount;
END $$;