PL/pgSQL: Delete (update) row matching a record

Question

I'm writing three triggers in PL/pgSQL. In each case, I have a RECORD variable and want to insert that into a table, delete it from the table, or update it to represent a second RECORD variable.

Adding is easy: INSERT INTO mytable VALUES (NEW.*);

Deleting isn't as easy, there doesn't seem to be a syntax for something like this:

DELETE FROM mytable
WHERE * = OLD.*;

Updating has the same problem. Is there an easy solution, short of generating matching SQL queries that compare each attribute using ideas from this answer?

Pavel Stehule · Accepted Answer · 2013-08-20T07:14:05.973

1

You can use a trick for delete

create table t(a int, b int);
create table ta(a int, b int);

create function t1_delete() 
returns trigger as $$
begin 
  delete from ta where ta = old; 
  return null; 
end
$$ language plpgsql;

But this trick doesn't work for UPDATE. So fully simple trigger in PL/pgSQL is not possible simply.

edited Aug 20 '13 at 07:14

answered Aug 20 '13 at 07:03

Pavel Stehule

42,331
5
91
94

Cool - didn't know that. By replacing UPDATE with DELETE + INSERT (which might not work correctly if there are triggers on that table), one could expand that to a fully working generic solution (except for the trigger problem). Thanks! – fefrei Aug 20 '13 at 18:56

score 1 · Answer 2 · edited May 23 '17 at 12:11

You write about a record variable and it is, indeed, not trivial to access individual columns of an anonymous record in plpgsql.

However, in your example, you only use OLD and NEW, which are well known row types, defined by the underlying table. It is trivial to access individual columns in this case.

DELETE FROM mytable
WHERE mytable_id = OLD.mytable_id;

UPDATE mytable_b
SET    some_col = NEW.some_other_col
WHERE  some_id  = NEW.mytable_id;

Etc.

Just be careful not to create endless loops.

In case you just want to "update" columns of the current row, you can simply assign to columns the NEW object in the trigger. You know that, right?

NEW.some_col := 'foo';

Dynamic column names

If you don't know column names beforehand, you can still do this generically with dynamic SQL as detailed in this related answer:
Update multiple columns in a trigger function in plpgsql

My problem with this approach is that I need to (manually) customize my trigger for each table to include the column names - I ended up writing a script to automatically generate these triggers for me. — fefrei, Aug 20 '13 at 18:54

PL/pgSQL: Delete (update) row matching a record

2 Answers2

Dynamic column names