sql - Postgresql 12 触发器更新与动态 SQL EXECUTE

Question

我有一个带有id和时间戳updated_at的父表订单：

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

我有子表order_products、order_delivery、order_billing、order_tracking等，每个表都有外键和order_id字段：

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

我想在每次更新子表中的字段时更新orders.updated_at 。但我不想为每个子表创建不同的触发函数，所以我创建了这个触发函数：

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$;

我使用TG_TABLE_NAME来查找触发触发器的子表。问题在于，对于特定 order_id 的任何子表的更新，它会更新所有“orders.updated_at”，而不仅仅是特定的“orders.id”。我应该在某处使用记录 NEW 但不知道如何，因为我正在使用动态 SQL 来分解触发器函数。任何想法 ？

解决方案 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$;

虽然不确定它是“干净”的 SQL

标签： sqlpostgresqldynamicsql-update

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$;