pg.ddx.io  pgsql-committers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Fix display of SQL-standard function's arguments in INSERT/SELEC
Date: Wed, 17 Nov 2021 16:31:50 +0000
Message-ID: <E1mnNqg-0007ik-RC@gemulon.postgresql.org> (raw)

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

If a SQL-standard function body contains an INSERT ... SELECT statement,
any function parameters referenced within the SELECT were always printed
in $N style, rather than using the parameter name if any.  While not
strictly incorrect, this wasn't the intention, and it's inconsistent
with the way that such parameters would be printed in any other kind
of statement.

The cause is that the recursion to get_query_def from
get_insert_query_def neglected to pass down the context->namespaces
list, passing constant NIL instead.  This is a very ancient oversight,
but AFAICT it had no visible consequences before commit e717a9a18
added an outermost namespace with function parameters.  We don't allow
INSERT ... SELECT as a sub-query, except in a top-level WITH clause,
where it couldn't contain any outer references that might need to access
upper namespaces.  So although that's arguably a bug, I don't see any
point in changing it before v14.

In passing, harden the code added to get_parameter by e717a9a18 so that
it won't crash if a PARAM_EXTERN Param appears in an unexpected place.

Per report from Erki Eessaar.  Code fix by me, regression test case
by Masahiko Sawada.

Discussion: https://postgr.es/m/AM9PR01MB8268347BED344848555167FAFE949@AM9PR01MB8268.eurprd01.prod.exchangelabs....

Branch
------
REL_14_STABLE

Details
-------
https://git.postgresql.org/pg/commitdiff/5d5779aeaf0780210de0e51dabeee8c9ceedb0ee

Modified Files
--------------
src/backend/utils/adt/ruleutils.c               | 10 ++++++----
src/test/regress/expected/create_function_3.out | 21 +++++++++++++++++++++
src/test/regress/sql/create_function_3.sql      | 11 +++++++++++
3 files changed, 38 insertions(+), 4 deletions(-)



view thread (2+ messages)  latest in thread

Message-ID: <E1mnNqg-0007ik-RC@gemulon.postgresql.org>
Permalink:  ../E1mnNqg-0007ik-RC@gemulon.postgresql.org/
Also on:    postgresql.org/message-id/E1mnNqg-0007ik-RC@gemulon.postgresql.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-committers@postgresql.org
  Cc: tgl@sss.pgh.pa.us
  Subject: Re: pgsql: Fix display of SQL-standard function's arguments in INSERT/SELEC
  In-Reply-To: <E1mnNqg-0007ik-RC@gemulon.postgresql.org>
  References: <E1mnNqg-0007ik-RC@gemulon.postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox