transition tables and UPDATE

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"