Postgresql: deleting from a view

postgresqlpostgresql-9.4triggerview

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!

Best Answer

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.

Related Question