In Postgresql 9.4, we're doing a view as a UNION of to tables A and B, and we'd like to have a single DELETED N ROWS message when we 'delete' the records in the underlying tables (via a a trigger on the view).
Here's more detailed info. Our views look like,
A -- id, name 1, afoo 2, abar B -- id, name 3, bfoo 4, bbar
So our view C looks like
C -- 1, afoo 2, abar 3, bfoo 4, bbar
We did a
CREATE TRIGGER delete_trigger INSTEAD OF DELETE C FOR EACH ROW DO delete_trigger(), which delete the rows by id which basically its like
-- our function trigger(): $$ delete from A where id=old.id; GET DIAGNOSTICS deletedA = ROW_COUNT; delete from B where id=old.id; GET DIAGNOSTICS deletedB = ROW_COUNT; raise notice 'deleted % records from A, % records from B', deletedA , deletedB; $$
If we issue a command like
delete * from C;, our ideal goal would be to have a single message that said
deleted 2 records from A, 2 records from B
But instead we have - because we don't know better -,
deleted 1 records from A, 0 records from B deleted 1 records from A, 0 records from B deleted 0 records from A, 1 records from B deleted 0 records from A, 0 records from B
Is it possible to get a single sum of deleted records?
We're also worried about the performance of this.
Any and all help greatly appreciated!