Earlier today I gave a talk about MERGE and wanted to provide an example
with FOR EACH STATEMENT triggers using transition tables. However, I
can't find a non-ugly way to obtain the NEW row that corresponds to each
OLD row ... I had to resort to an ugly trick with OFFSET n LIMIT 1.
Can anyone suggest anything better? I couldn't find any guidance in the
docs.
This is the example function I wrote:
CREATE FUNCTION wine_audit() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
IF (TG_OP = 'DELETE') THEN
INSERT INTO wine_audit
SELECT 'D', now(), row_to_json(o), NULL FROM old_table o;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO wine_audit
SELECT 'I', now(), NULL, row_to_json(n) FROM new_table n;
ELSIF (TG_OP = 'UPDATE') THEN
DECLARE
oldrec record;
newrec jsonb;
i integer := 0;
BEGIN
FOR oldrec IN SELECT * FROM old_table LOOP
newrec := row_to_json(n) FROM new_table n OFFSET i LIMIT 1;
i := i + 1;
INSERT INTO wine_audit
SELECT 'U', now(), row_to_json(oldrec), newrec;
END LOOP;
END;
END IF;
RETURN NULL;
END;
$$;
CREATE TABLE wines (winery text, brand text, variety text, year int, bottles
int);
CREATE TABLE shipment (LIKE wines);
CREATE TABLE wine_audit (op varchar(1), datetime timestamptz,
oldrow jsonb, newrow jsonb);
CREATE TRIGGER wine_update
AFTER UPDATE ON wines
REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
FOR EACH STATEMENT EXECUTE FUNCTION wine_audit();
-- I omit triggers on insert and update because the trigger code for those is
trivial
INSERT INTO wines VALUES ('Concha y Toro', 'Sunrise', 'Chardonnay', 2021, 12),
('Concha y Toro', 'Sunrise', 'Merlot', 2022, 12);
INSERT INTO shipment VALUES ('Concha y Toro', 'Sunrise', 'Chardonnay', 2021,
96),
('Concha y Toro', 'Sunrise', 'Merlot', 2022, 120),
('Concha y Toro', 'Marqués de Casa y Concha', 'Carmenere', 2021, 48),
('Concha y Toro', 'Casillero del Diablo', 'Cabernet Sauvignon', 2019, 240);
ALTER TABLE shipment ADD COLUMN marked timestamp with time zone;
WITH unmarked_shipment AS
(UPDATE shipment SET marked = now() WHERE marked IS NULL
RETURNING winery, brand, variety, year, bottles)
MERGE INTO wines AS w
USING (SELECT winery, brand, variety, year,
sum(bottles) as bottles
FROM unmarked_shipment
GROUP BY winery, brand, variety, year) AS s
ON (w.winery, w.brand, w.variety, w.year) =
(s.winery, s.brand, s.variety, s.year)
WHEN MATCHED THEN
UPDATE SET bottles = w.bottles + s.bottles
WHEN NOT MATCHED THEN
INSERT (winery, brand, variety, year, bottles)
VALUES (s.winery, s.brand, s.variety, s.year, s.bottles)
;
If you examine table wine_audit after pasting all of the above, you'll
see this, which is correct:
─[ RECORD 1
]────────────────────────────────────────────────────────────────────────────────────────────────────
op │ U
datetime │ 2023-02-01 01:16:44.704036+01
oldrow │ {"year": 2021, "brand": "Sunrise", "winery": "Concha y Toro",
"bottles": 12, "variety": "Chardonnay"}
newrow │ {"year": 2021, "brand": "Sunrise", "winery": "Concha y Toro",
"bottles": 108, "variety": "Chardonnay"}
─[ RECORD 2
]────────────────────────────────────────────────────────────────────────────────────────────────────
op │ U
datetime │ 2023-02-01 01:16:44.704036+01
oldrow │ {"year": 2022, "brand": "Sunrise", "winery": "Concha y Toro",
"bottles": 12, "variety": "Merlot"}
newrow │ {"year": 2022, "brand": "Sunrise", "winery": "Concha y Toro",
"bottles": 132, "variety": "Merlot"}
My question is how to obtain the same rows without the LIMIT/OFFSET line
in the trigger function.
Also: how can we "subtract" both JSON blobs so that the 'newrow' only
contains the members that differ? I would like to have this:
─[ RECORD 1
]────────────────────────────────────────────────────────────────────────────────────────────────────
op │ U
datetime │ 2023-02-01 01:16:44.704036+01
oldrow │ {"year": 2021, "brand": "Sunrise", "winery": "Concha y Toro",
"bottles": 12, "variety": "Chardonnay"}
newrow │ {"bottles": 108}
─[ RECORD 2
]────────────────────────────────────────────────────────────────────────────────────────────────────
op │ U
datetime │ 2023-02-01 01:16:44.704036+01
oldrow │ {"year": 2022, "brand": "Sunrise", "winery": "Concha y Toro",
"bottles": 12, "variety": "Merlot"}
newrow │ {"bottles": 132}
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"La gente vulgar sólo piensa en pasar el tiempo;
el que tiene talento, en aprovecharlo"
