Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commita8d8445

Browse files
committed
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 printedin $N style, rather than using the parameter name if any. While notstrictly incorrect, this wasn't the intention, and it's inconsistentwith the way that such parameters would be printed in any other kindof statement.The cause is that the recursion to get_query_def fromget_insert_query_def neglected to pass down the context->namespaceslist, passing constant NIL instead. This is a very ancient oversight,but AFAICT it had no visible consequences before commite717a9aadded an outermost namespace with function parameters. We don't allowINSERT ... SELECT as a sub-query, except in a top-level WITH clause,where it couldn't contain any outer references that might need to accessupper namespaces. So although that's arguably a bug, I don't see anypoint in changing it before v14.In passing, harden the code added to get_parameter bye717a9a so thatit won't crash if a PARAM_EXTERN Param appears in an unexpected place.Per report from Erki Eessaar. Code fix by me, regression test caseby Masahiko Sawada.Discussion:https://postgr.es/m/AM9PR01MB8268347BED344848555167FAFE949@AM9PR01MB8268.eurprd01.prod.exchangelabs.com
1 parentaa12781 commita8d8445

File tree

3 files changed

+38
-4
lines changed

3 files changed

+38
-4
lines changed

‎src/backend/utils/adt/ruleutils.c

Lines changed: 6 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -6566,7 +6566,7 @@ get_insert_query_def(Query *query, deparse_context *context)
65666566
if (select_rte)
65676567
{
65686568
/* Add the SELECT */
6569-
get_query_def(select_rte->subquery,buf,NIL,NULL,
6569+
get_query_def(select_rte->subquery,buf,context->namespaces,NULL,
65706570
context->prettyFlags,context->wrapColumn,
65716571
context->indentLevel);
65726572
}
@@ -7919,10 +7919,12 @@ get_parameter(Param *param, deparse_context *context)
79197919
* If it's an external parameter, see if the outermost namespace provides
79207920
* function argument names.
79217921
*/
7922-
if (param->paramkind==PARAM_EXTERN)
7922+
if (param->paramkind==PARAM_EXTERN&&context->namespaces!=NIL)
79237923
{
7924-
dpns=lfirst(list_tail(context->namespaces));
7925-
if (dpns->argnames)
7924+
dpns=llast(context->namespaces);
7925+
if (dpns->argnames&&
7926+
param->paramid>0&&
7927+
param->paramid <=dpns->numargs)
79267928
{
79277929
char*argname=dpns->argnames[param->paramid-1];
79287930

‎src/test/regress/expected/create_function_3.out

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -279,6 +279,13 @@ CREATE FUNCTION functest_S_13() RETURNS boolean
279279
SELECT 1;
280280
SELECT false;
281281
END;
282+
-- check display of function argments in sub-SELECT
283+
CREATE TABLE functest1 (i int);
284+
CREATE FUNCTION functest_S_16(a int, b int) RETURNS void
285+
LANGUAGE SQL
286+
BEGIN ATOMIC
287+
INSERT INTO functest1 SELECT a + $2;
288+
END;
282289
-- error: duplicate function body
283290
CREATE FUNCTION functest_S_xxx(x int) RETURNS int
284291
LANGUAGE SQL
@@ -417,6 +424,20 @@ SELECT pg_get_functiondef('functest_S_15'::regproc);
417424

418425
(1 row)
419426

427+
SELECT pg_get_functiondef('functest_S_16'::regproc);
428+
pg_get_functiondef
429+
-------------------------------------------------------------------------------
430+
CREATE OR REPLACE FUNCTION temp_func_test.functest_s_16(a integer, b integer)+
431+
RETURNS void +
432+
LANGUAGE sql +
433+
BEGIN ATOMIC +
434+
INSERT INTO functest1 (i) SELECT (functest_s_16.a + functest_s_16.b); +
435+
END +
436+
437+
(1 row)
438+
439+
DROP TABLE functest1 CASCADE;
440+
NOTICE: drop cascades to function functest_s_16(integer,integer)
420441
-- test with views
421442
CREATE TABLE functest3 (a int);
422443
INSERT INTO functest3 VALUES (1), (2);

‎src/test/regress/sql/create_function_3.sql

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -180,6 +180,14 @@ CREATE FUNCTION functest_S_13() RETURNS boolean
180180
SELECT false;
181181
END;
182182

183+
-- check display of function argments in sub-SELECT
184+
CREATETABLEfunctest1 (iint);
185+
CREATEFUNCTIONfunctest_S_16(aint, bint) RETURNS void
186+
LANGUAGE SQL
187+
BEGIN ATOMIC
188+
INSERT INTO functest1SELECT a+ $2;
189+
END;
190+
183191
-- error: duplicate function body
184192
CREATEFUNCTIONfunctest_S_xxx(xint) RETURNSint
185193
LANGUAGE SQL
@@ -217,6 +225,9 @@ SELECT pg_get_functiondef('functest_S_3a'::regproc);
217225
SELECT pg_get_functiondef('functest_S_10'::regproc);
218226
SELECT pg_get_functiondef('functest_S_13'::regproc);
219227
SELECT pg_get_functiondef('functest_S_15'::regproc);
228+
SELECT pg_get_functiondef('functest_S_16'::regproc);
229+
230+
DROPTABLE functest1 CASCADE;
220231

221232
-- test with views
222233
CREATETABLEfunctest3 (aint);

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp