Re: Adding OLD/NEW support to RETURNING

On Sat, 16 Dec 2023 at 13:04, jian he <jian(dot)universality(at)gmail(dot)com> wrote:
>
> /* get the tuple from the relation being scanned */
> I have roughly an idea of what this code is doing. but do you need to
> refactor the above comment?
>
> /* for EEOP_INNER/OUTER/SCAN_FETCHSOME */
> in src/backend/executor/execExpr.c, do you need to update the comment?
>

Thanks for looking at this.

Attached is a new version with some updated comments. In addition, I
fixed a couple of issues:

In raw_expression_tree_walker(), I had missed one of the new node types.

When "old" or "new" are specified by themselves in the RETURNING list
to return the whole old/new row, the parser was generating a RowExpr
node, which appeared to work OK, but failed if there were any dropped
columns in the relation. I have changed this to generate a wholerow
Var instead, which deals with that issue, and seems better for
efficiency and consistency with existing code.

In addition, I have added code during executor startup to record
whether or not the RETURNING list actually has any references to
OLD/NEW values. This allows the building of old/new tuple slots to be
skipped when they're not actually needed, reducing per-row overheads.

I still haven't written any docs yet.

> create or replace function stricttest2() returns void as $$
> declare x record; y record;
> begin
> INSERT INTO foo select 11, 22 RETURNING WITH (old AS o, new AS n)
> o into x, n into y;
> raise notice 'x.f1: % x.f2 % y.f1 % y.f2 %', x.f1,x.f2, y.f1, y.f2;
> end$$ language plpgsql;
> --this does not work.
> --because https://www.postgresql.org/message-id/flat/CAFj8pRB76FE2MVxJYPc1RvXmsf2upoTgoPCC9GsvSAssCM2APQ%40mail.gmail.com
>
> create or replace function stricttest5() returns void as $$
> declare x record; y record;
> a foo%ROWTYPE; b foo%ROWTYPE;
> begin
> INSERT INTO foo select 11, 22
> RETURNING WITH (old AS o, new AS n) o into a, n into b;
> end$$ language plpgsql;
> -- expect this to work.

Yeah, but note that multiple INTO clauses aren't allowed. An
alternative is to create a custom type to hold the old and new
records, e.g.:

CREATE TYPE foo_delta AS (old foo, new foo);

then you can just do "RETURNING old, new INTO delta" where delta is a
variable of type foo_delta, and you can extract individual fields
using expressions like "(delta.old).f1".

Regards,
Dean