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
