postgresql: deleting from a view

Question

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!

Answer 1

The triggered event is invoked for each row.

The syntax is

CREATE TRIGGER c_view_delete_trg INSTEAD OF DELETE ON c_view FOR EACH ROW EXECUTE PROCEDURE c_view_delete();

This means that the function is invoked once for each row that is being deleted. this is why it prints each time one row.

• Randi Vertongen: I'd say that his question is more in the sense of how do we do it in one batch, instead of row by row. If that is possible.
• Yaki Klein: Don't think that is possible. Instead of statements are row-level statements. Take a look here postgresql.org/docs/10/sql-createtrigger.html
• Matthieu: Why wouldn't FOR EACH STATEMENT (which is the default) give the expected result?