Re: MERGE ... RETURNING

On Thu, 13 Jul 2023 at 20:14, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
>
> On Thu, 2023-07-13 at 18:01 +0100, Dean Rasheed wrote:
> > For some use cases, I can imagine allowing OLD/NEW.colname would mean
> > you wouldn't need pg_merge_action() (if the column was NOT NULL), so
> > I
> > think the features should work well together.
>
> For use cases where a user could do it either way, which would you
> expect to be the "typical" way (assuming we supported the new/old)?
>
> MERGE ... RETURNING pg_merge_action(), id, val;
>
> or
>
> MERGE ... RETURNING id, OLD.val, NEW.val;
>
> ?
>

I think it might depend on whether OLD.val and NEW.val were actually
required, but I think I would still probably use pg_merge_action() to
get the action, since it doesn't rely on specific table columns being
NOT NULL. It's a little like writing a trigger function that handles
multiple command types. You could use OLD and NEW to deduce whether it
was an INSERT, UPDATE or DELETE, or you could use TG_OP. I tend to use
TG_OP, but maybe there are situations where using OLD and NEW is more
natural.

I found a 10-year-old thread discussing adding support for OLD/NEW to
RETURNING [1], but it doesn't look like anything close to a
committable solution was developed, or even a design that might lead
to one. That's a shame, because there seemed to be a lot of demand for
the feature, but it's not clear how much effort it would be to
implement.

> I am still bothered that pg_merge_action() is so context-sensitive.
> "SELECT pg_merge_action()" by itself doesn't make any sense, but it's
> allowed in the v8 patch. We could make that a runtime error, which
> would be better, but it feels like it's structurally wrong. This is not
> an objection, but it's just making me think harder about alternatives.
>
> Maybe instead of a function it could be a special table reference like:
>
> MERGE ... RETURNING MERGE.action, MERGE.action_number, id, val?
>

Well, that's a little more concise, but I'm not sure that it really
buys us that much, to be worth the extra complication. Presumably
something in the planner would turn that into something the executor
could handle, which might just end up being the existing functions
anyway.

Regards,
Dean

[1] https://www.postgresql.org/message-id/flat/51822C0F.5030807%40gmail.com