Re: transition tables and UPDATE

On 2023-Feb-01, Thomas Munro wrote:

> On Wed, Feb 1, 2023 at 10:18 PM Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> wrote:
> > Earlier today I gave a talk about MERGE and wanted to provide an example
> > with FOR EACH STATEMENT triggers using transition tables. However, I
> > can't find a non-ugly way to obtain the NEW row that corresponds to each
> > OLD row ... I had to resort to an ugly trick with OFFSET n LIMIT 1.
> > Can anyone suggest anything better? I couldn't find any guidance in the
> > docs.
>
> I don't know the answer, either in PostgreSQL or the SQL spec. I
> wondered if there *should* be a way here:
>
> https://www.postgresql.org/message-id/CAEepm=1ncxBNna-pXGr2hnMHRyYi_6_AwG_352-Jn=mwdFdAGw@mail.gmail.com

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.

--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
"The important things in the world are problems with society that we don't
understand at all. The machines will become more complicated but they won't
be more complicated than the societies that run them." (Freeman Dyson)