Re: transition tables and UPDATE

On Wed, 1 Feb 2023 at 12:12, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
>
> I had tried to tie these relations using WITH ORDINALITY, but the only
> way I could think of (array_agg to then unnest() WITH ORDINALITY) was
> even uglier than what I already had.  So yeah, I think it might be
> useful if we had a way to inject a counter or something in there.
>

You could use a pair of cursors like this:

CREATE OR REPLACE 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
        oldcur CURSOR FOR SELECT row_to_json(o) FROM old_table o;
        newcur CURSOR FOR SELECT row_to_json(n) FROM new_table n;
        oldrec jsonb;
        newrec jsonb;
      BEGIN
        OPEN oldcur;
        OPEN newcur;

        LOOP
          FETCH oldcur INTO oldrec;
          EXIT WHEN NOT FOUND;

          FETCH newcur INTO newrec;
          EXIT WHEN NOT FOUND;

          INSERT INTO wine_audit VALUES('U', now(), oldrec, newrec);
        END LOOP;

        CLOSE oldcur;
        CLOSE newcur;
      END;

    END IF;
    RETURN NULL;
  END;
$$;

though it would be nicer if there was a way to do it in a single SQL statement.

Regards,
Dean