Is there anyway to associate rows between the old transition table and new transition table for a per-statement update trigger in Postgres? docs
My use-case is that I have a "shadow" table of an existing, concrete table. I'd like to update the shadow table on every UPDATE statement on the concrete table. I know I can use a per-row trigger to accomplish this. Can I use a per-statement trigger instead?
As a concrete example on Postgres 13.3, I'd like something like:
CREATE TABLE product (id bigint, val text /* many more columns */); CREATE TABLE shadow_product (id bigint, val text); CREATE FUNCTION update_shadow_product() RETURNS trigger AS $fn$ DECLARE old_data text; new_data text; BEGIN UPDATE shadow_product SET id = t.old_id, val = t.new_val FROM ( -- How to handle case where ID changes between old and new? SELECT ot.id as old_id, nt.val as new_val FROM oldtab ot INNER JOIN newtab nt USING (id) ) t WHERE id = t.old_id; RETURN NULL; -- per-statement triggers should always return null END; $fn$ LANGUAGE plpgsql; CREATE TRIGGER update_shadow_products_trigger AFTER UPDATE ON product REFERENCING NEW TABLE AS newtab OLD TABLE AS oldtab FOR EACH STATEMENT EXECUTE FUNCTION update_shadow_product();
As a test case:
-- Seed initial data. INSERT INTO product (id, val) VALUES (1, 2), (10, 20); INSERT INTO shadow_product SELECT id, val FROM product; -- Update the primary key which makes it hard to match old to new rows. UPDATE product SET id = 2 * id WHERE TRUE; -- Show rows that differ between product and shadow_product. -- Ideally there's no null values, indicating the tables are the same. SELECT p.id, p.val, sp.id AS shadow_id, sp.val AS shadow_val FROM product p FULL OUTER JOIN shadow_product sp USING (id, val) /* * Returns: * * +----+----+---------+----------+ * |id |val |shadow_id|shadow_val| * +----+----+---------+----------+ * |NULL|NULL|1 |2 | * |2 |2 |NULL |NULL | * |NULL|NULL|10 |20 | * |20 |20 |NULL |NULL | * +----+----+---------+----------+ */
I'm not sure sure how to associate rows between
newtab if primary key changes. One thought I had was relying on
newtab without an order clause. Some quick tests seem to indicate the row_number order is stable but I'd prefer a more bulletproof method. Is there a better way?