Horton

Re: References to parameters by name are lost in INSERT INTO ... SELECT <parameter value> .... statements in case of routines with the SQL-standard function body

Masahiko Sawada <sawada.mshk@gmail.com>

View thread
From: Masahiko Sawada <sawada.mshk@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Erki Eessaar <erki.eessaar@taltech.ee>, "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>, Peter Eisentraut <peter.eisentraut@enterprisedb.com>
Date: 2021-11-17T01:06:15Z
Lists: pgsql-bugs

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Fix display of SQL-standard function's arguments in INSERT/SELECT.

  2. SQL-standard function body

Attachments

On Wed, Nov 17, 2021 at 6:52 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Erki Eessaar <erki.eessaar@taltech.ee> writes:
> > Indeed, re-execution of this code without any modifications in it produces the same result.
>
> Right, that printout is functionally equivalent to the original.
>
> > Still I see here two problems.
> >   *   Inconsistency - see INSERT vs. UPDATE.
>
> Yeah, it's weird that the same parameter is printed two different ways.
> I dug into it and found out that we're losing the "context->namespace"
> list when recursing into the sub-SELECT from get_insert_query_def.
> The fix is trivial (attached).  The other places where get_query_def is
> invoked quasi-recursively all pass down the parent namespace list already.
> The fact that this one is out of step is a very ancient oversight (it's
> at least old enough to vote, according to some quick git archaeology).
> But as far as I can see, it didn't have any visible consequences until
> commit e717a9a18 taught get_parameter() to pay attention to the last
> entry of the list.  So I'm inclined not to change it before v14.

Agreed. I've confirmed by the attached test that the patch fixes this issue.

Regards,

-- 
Masahiko Sawada
EDB:  https://www.enterprisedb.com/