Feedback-ID: ia2694551:Fastmail
Date: Wed, 1 Feb 2023 10:03:26 +0100
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Pg Hackers <pgsql-hackers@lists.postgresql.org>
Subject: transition tables and UPDATE
Message-ID: <20230201090326.aolrmpoy5cbcjslq@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/20230201090326.aolrmpoy5cbcjslq%40alvherre.pgsql>
Precedence: bulk

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"