4

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!

2

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.

3
  • 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. – Randi Vertongen Dec 24 '18 at 12:52
  • Don't think that is possible. Instead of statements are row-level statements. Take a look here postgresql.org/docs/10/sql-createtrigger.html – Yaki Klein Dec 24 '18 at 13:11
  • Why wouldn't FOR EACH STATEMENT (which is the default) give the expected result? – Matthieu Apr 23 '20 at 15:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.