Re: transition tables and UPDATE

On Wed, 1 Feb 2023 at 12:12, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)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