Postgresql 12 trigger updatewith dynamic SQL EXECUTE

Question

I have a parent table orders with an id and a timestamp updated_at :

+-----+-------------------------------+
| id  |          updated_at           |
+-----+-------------------------------+
| 808 | 2020-09-23 15:14:13.077086+02 |
+-----+-------------------------------+

I have child tables order_products, order_delivery, order_billing, order_tracking etc. with foreign keys and order_id field for each :

+----------+---------+---------+---------+
| order_id | field_x | field_y | field_z |
+----------+---------+---------+---------+
|      808 | x       | y       | z       |
+----------+---------+---------+---------+

I want to update orders.updated_at each time a field is updated in a child table. But I do not want to create a different trigger function for each child table, so I created this trigger function :

CREATE OR REPLACE FUNCTION public.update_orders_updated_at_from_sub_orders()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
BEGIN
    EXECUTE format('UPDATE orders SET updated_at = NOW() FROM %I WHERE orders.id = %I.order_id;',TG_TABLE_NAME,TG_TABLE_NAME);
    RETURN NEW;
END;
$BODY$;

I use TG_TABLE_NAME to find on which child table the trigger is triggred. The problem is that for an update on any child table for a specific order_id, it updates ALL "orders.updated_at" and not only the specific "orders.id". I should use the record NEW somewhere but can't figure out how because I'm using dynamic SQL to factorise the trigger function. Any idea ?

SOLUTION 1 :

CREATE OR REPLACE FUNCTION public.update_orders_updated_at_from_sub_orders()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
    DECLARE new_order_id INTEGER;
BEGIN
    new_order_id = NEW.order_id;
    EXECUTE format('UPDATE orders SET updated_at = NOW() FROM %I WHERE orders.id = %I.order_id AND orders.id = %s::INT;',TG_TABLE_NAME,TG_TABLE_NAME,new_order_id);
    RETURN NEW;
END;
$BODY$;

Not sure it is "clean" SQL though

question from:https://stackoverflow.com/questions/65598335/postgresql-12-trigger-updatewith-dynamic-sql-execute

Answer 1

CREATE OR REPLACE FUNCTION public.update_orders_updated_at_from_sub_orders()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
BEGIN
    UPDATE orders SET updated_at = NOW() WHERE id = NEW.order_id;
    RETURN NEW;
END;
$BODY$;