Re: transition tables and UPDATE

On Wed, 1 Feb 2023 10:03:26 +0100
Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> wrote:

> 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.

What I could come up with is to join old_table and new_table using keys
of the wine table (winery, brand, variety, year), or join them using
values from row_number(), like;

INSERT INTO wine_audit
SELECT 'U', now(), row_o, row_n
FROM (SELECT row_number() OVER() i, row_to_json(o) row_o FROM old_table o)
JOIN (SELECT row_number() OVER() i, row_to_json(n) row_n FROM new_table n)
USING (i);

>
> 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"
>
>

--
Yugo NAGATA <nagata(at)sraoss(dot)co(dot)jp>