postgres.git / summary / log / commit / refs
commit c6e70d5d37557a22fd41fbd6dae9d71ac4545129
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date: Thu Nov 12 19:53:11 2026 +0000
First released in: 19.0
Fix assorted confusion about parameter names.
New-style SQL-language functions failed quite badly if named input
parameters were declared after any output parameters, which is unusual
style but perfectly legal. The parser failed to recognize some of the
input parameter names, and could instead accept output parameter names
if there were any, and assigned the wrong parameter numbers in any
case. ruleutils.c was equally confused and would print the wrong
parameter names when deparsing a new-style function.
This mostly comes down to using get_func_arg_info() and failing to
account for the fact that its output covers both input and output
parameters. In some places the right answer is to use
get_func_input_arg_names() instead, although that has an API just
different enough to trip you up. (I added some comments about that.)
Examining other callers of get_func_arg_info() found some related
breakage: PL/Python didn't do quite the right thing with unnamed
input parameters, and plsample.c was completely broken for any
case involving output parameters at all.
A remaining issue, not dealt with here, is that ruleutils' habit
of deparsing Param references using qualified parameter names is
vulnerable to aliasing problems: if the function name matches
some table alias used within the function then we can emit output
that means something different than we intended.
Bug: #19848
Reported-by: Atish Prakash <125atishprak@gmail.com>
Diagnosed-by: Atish Prakash <125atishprak@gmail.com>
Author: Tom Lane <tgl@sss.pgh.pa.us>
Discussion: https://postgr.es/m/19848-c4d5bc38ad4ab8b8@postgresql.org
Backpatch-through: 15
src/backend/commands/functioncmds.c | 9 +--
src/backend/utils/adt/ruleutils.c | 30 ++++++----
src/backend/utils/fmgr/funcapi.c | 6 +-
src/pl/plpython/plpy_procedure.c | 3 +-
src/test/modules/plsample/plsample.c | 20 +++++--
src/test/regress/expected/create_function_sql.out | 73 ++++++++++++++++++++++-
src/test/regress/sql/create_function_sql.sql | 32 ++++++++++
7 files changed, 152 insertions(+), 21 deletions(-)
diff --git a/src/backend/commands/functioncmds.c b/src/backend/commands/functioncmds.c
index 5a6ea0f3286..1d263ae4057 100644
--- a/src/backend/commands/functioncmds.c
+++ b/src/backend/commands/functioncmds.c
@@ -322,6 +322,9 @@ interpret_function_parameter_list(ParseState *pstate,
isinput = true;
if (parameterTypes_list)
*parameterTypes_list = lappend_oid(*parameterTypes_list, toid);
+ if (inParameterNames_list)
+ *inParameterNames_list = lappend(*inParameterNames_list,
+ makeString(fp->name ? fp->name : pstrdup("")));
}
/* handle output parameters */
@@ -418,9 +421,6 @@ interpret_function_parameter_list(ParseState *pstate,
have_names = true;
}
- if (inParameterNames_list)
- *inParameterNames_list = lappend(*inParameterNames_list, makeString(fp->name ? fp->name : pstrdup("")));
-
if (fp->defexpr)
{
Node *def;
@@ -930,9 +930,10 @@ interpret_AS_clause(Oid languageOid, const char *languageName,
pinfo->fname = funcname;
pinfo->nargs = list_length(parameterTypes);
+ Assert(pinfo->nargs == list_length(inParameterNames));
pinfo->argtypes = (Oid *) palloc(pinfo->nargs * sizeof(Oid));
pinfo->argnames = (char **) palloc(pinfo->nargs * sizeof(char *));
- for (int i = 0; i < list_length(parameterTypes); i++)
+ for (int i = 0; i < pinfo->nargs; i++)
{
char *s = strVal(list_nth(inParameterNames, i));
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 9054773fe6a..76c3053bb1c 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -182,8 +182,8 @@ typedef struct
List *index_tlist; /* referent for INDEX_VAR Vars */
/* Special namespace representing a function signature: */
char *funcname;
- int numargs;
- char **argnames;
+ int numargs; /* number of input arguments */
+ char **argnames; /* names of input args (NULL if unnamed) */
} deparse_namespace;
/*
@@ -3561,19 +3561,29 @@ pg_get_function_arg_default(PG_FUNCTION_ARGS)
static void
print_function_sqlbody(StringInfo buf, HeapTuple proctup)
{
- int numargs;
- Oid *argtypes;
- char **argnames;
- char *argmodes;
+ Datum proargnames;
+ Datum proargmodes;
+ bool isNull;
deparse_namespace dpns = {0};
Datum tmp;
Node *n;
dpns.funcname = pstrdup(NameStr(((Form_pg_proc) GETSTRUCT(proctup))->proname));
- numargs = get_func_arg_info(proctup,
- &argtypes, &argnames, &argmodes);
- dpns.numargs = numargs;
- dpns.argnames = argnames;
+
+ proargnames = SysCacheGetAttr(PROCOID, proctup,
+ Anum_pg_proc_proargnames,
+ &isNull);
+ if (isNull)
+ proargnames = PointerGetDatum(NULL); /* just to be sure */
+
+ proargmodes = SysCacheGetAttr(PROCOID, proctup,
+ Anum_pg_proc_proargmodes,
+ &isNull);
+ if (isNull)
+ proargmodes = PointerGetDatum(NULL); /* just to be sure */
+
+ dpns.numargs = get_func_input_arg_names(proargnames, proargmodes,
+ &dpns.argnames);
tmp = SysCacheGetAttrNotNull(PROCOID, proctup, Anum_pg_proc_prosqlbody);
n = stringToNode(TextDatumGetCString(tmp));
diff --git a/src/backend/utils/fmgr/funcapi.c b/src/backend/utils/fmgr/funcapi.c
index 5c56002ba0e..5cc6c52d36d 100644
--- a/src/backend/utils/fmgr/funcapi.c
+++ b/src/backend/utils/fmgr/funcapi.c
@@ -1375,6 +1375,9 @@ get_type_func_class(Oid typid, Oid *base_typeid)
* *p_argnames and *p_argmodes will be set NULL in the default cases
* (no names, and all IN arguments, respectively).
*
+ * If there are both named and unnamed arguments, p_argnames[] will
+ * contain empty strings (not NULLs) for the unnamed ones.
+ *
* Note that this function simply fetches what is in the pg_proc tuple;
* it doesn't do any interpretation of polymorphic types.
*/
@@ -1519,7 +1522,8 @@ get_func_trftypes(HeapTuple procTup,
*
* Returns the number of input arguments, which is the length of the
* palloc'd array returned to *arg_names. Entries for unnamed args
- * are set to NULL. You don't get anything if proargnames is NULL.
+ * are set to NULL (note the inconsistency with get_func_arg_info!).
+ * You don't get anything if proargnames is NULL.
*/
int
get_func_input_arg_names(Datum proargnames, Datum proargmodes,
diff --git a/src/pl/plpython/plpy_procedure.c b/src/pl/plpython/plpy_procedure.c
index d06c5505a91..6d24633756d 100644
--- a/src/pl/plpython/plpy_procedure.c
+++ b/src/pl/plpython/plpy_procedure.c
@@ -348,7 +348,8 @@ PLy_procedure_create(PLyProcedure *proc,
proc);
/* get argument name */
- proc->argnames[pos] = names ? pstrdup(names[i]) : NULL;
+ proc->argnames[pos] =
+ (names && names[i][0] != '\0') ? pstrdup(names[i]) : NULL;
ReleaseSysCache(argTypeTup);
diff --git a/src/test/modules/plsample/plsample.c b/src/test/modules/plsample/plsample.c
index 31ef4c96f22..1e817ceee5a 100644
--- a/src/test/modules/plsample/plsample.c
+++ b/src/test/modules/plsample/plsample.c
@@ -111,6 +111,7 @@ plsample_func_handler(PG_FUNCTION_ARGS)
Oid prorettype;
FmgrInfo result_in_func;
int numargs;
+ int inargno;
/* Fetch the function's pg_proc entry. */
pl_tuple = SearchSysCache1(PROCOID,
@@ -149,23 +150,34 @@ plsample_func_handler(PG_FUNCTION_ARGS)
* Iterate through all of the function arguments, printing each input
* value.
*/
+ inargno = 0;
for (int i = 0; i < numargs; i++)
{
- Oid argtype = pl_struct->proargtypes.values[i];
+ Oid argtype = argtypes[i];
char *value;
+ if (argmodes &&
+ (argmodes[i] == PROARGMODE_OUT ||
+ argmodes[i] == PROARGMODE_TABLE))
+ continue; /* skip OUT arguments */
+
type_tuple = SearchSysCache1(TYPEOID, ObjectIdGetDatum(argtype));
if (!HeapTupleIsValid(type_tuple))
elog(ERROR, "cache lookup failed for type %u", argtype);
type_struct = (Form_pg_type) GETSTRUCT(type_tuple);
- fmgr_info_cxt(type_struct->typoutput, &(arg_out_func[i]), proc_cxt);
+ fmgr_info_cxt(type_struct->typoutput,
+ &(arg_out_func[inargno]), proc_cxt);
ReleaseSysCache(type_tuple);
- value = OutputFunctionCall(&arg_out_func[i], fcinfo->args[i].value);
+ value = OutputFunctionCall(&arg_out_func[inargno],
+ fcinfo->args[inargno].value);
ereport(NOTICE,
(errmsg("argument: %d; name: %s; value: %s",
- i, argnames[i], value)));
+ inargno,
+ (argnames && argnames[i][0]) ? argnames[i] : "(none)",
+ value)));
+ inargno++;
}
/* Type of the result */
diff --git a/src/test/regress/expected/create_function_sql.out b/src/test/regress/expected/create_function_sql.out
index df7188d3de2..57a86927047 100644
--- a/src/test/regress/expected/create_function_sql.out
+++ b/src/test/regress/expected/create_function_sql.out
@@ -485,6 +485,73 @@ SELECT pg_get_functiondef('functest_S_16'::regproc);
DROP TABLE functest1 CASCADE;
NOTICE: drop cascades to function functest_s_16(integer,integer)
+-- check cases with OUT parameters before IN ones
+CREATE FUNCTION functest_out_point(OUT p point, x float8, y float8)
+LANGUAGE sql AS $$ select point(x,y); $$;
+SELECT functest_out_point(11,22);
+ functest_out_point
+--------------------
+ (11,22)
+(1 row)
+
+CREATE FUNCTION functest_out_point2(OUT p point, float8, y float8)
+LANGUAGE sql AS $$ select point($1,y); $$;
+SELECT functest_out_point2(11,22);
+ functest_out_point2
+---------------------
+ (11,22)
+(1 row)
+
+CREATE FUNCTION functest_out_point_sql(OUT p point, x float8, y float8)
+BEGIN ATOMIC SELECT point(x,y); END;
+SELECT functest_out_point_sql(11,22);
+ functest_out_point_sql
+------------------------
+ (11,22)
+(1 row)
+
+SELECT pg_get_functiondef('functest_out_point_sql'::regproc);
+ pg_get_functiondef
+-----------------------------------------------------------------------------------------------------------------------
+ CREATE OR REPLACE FUNCTION temp_func_test.functest_out_point_sql(OUT p point, x double precision, y double precision)+
+ RETURNS point +
+ LANGUAGE sql +
+ BEGIN ATOMIC +
+ SELECT point(x, y) AS point; +
+ END +
+
+(1 row)
+
+CREATE FUNCTION functest_out_point2_sql(OUT p point, float8, y float8)
+BEGIN ATOMIC SELECT point($1,y); END;
+SELECT functest_out_point2_sql(11,22);
+ functest_out_point2_sql
+-------------------------
+ (11,22)
+(1 row)
+
+SELECT pg_get_functiondef('functest_out_point2_sql'::regproc);
+ pg_get_functiondef
+----------------------------------------------------------------------------------------------------------------------
+ CREATE OR REPLACE FUNCTION temp_func_test.functest_out_point2_sql(OUT p point, double precision, y double precision)+
+ RETURNS point +
+ LANGUAGE sql +
+ BEGIN ATOMIC +
+ SELECT point($1, y) AS point; +
+ END +
+
+(1 row)
+
+CREATE FUNCTION functest_out_error(OUT p point, x float8, y float8)
+LANGUAGE sql AS $$ select point(p,x); $$;
+ERROR: column "p" does not exist
+LINE 2: LANGUAGE sql AS $$ select point(p,x); $$;
+ ^
+CREATE FUNCTION functest_out_error_sql(OUT p point, x float8, y float8)
+BEGIN ATOMIC SELECT point(p,x); END;
+ERROR: column "p" does not exist
+LINE 2: BEGIN ATOMIC SELECT point(p,x); END;
+ ^
-- test with views
CREATE TABLE functest3 (a int);
INSERT INTO functest3 VALUES (1), (2);
@@ -846,7 +913,7 @@ CONTEXT: SQL function "test1" during startup
RESET check_function_bodies;
-- Cleanup
DROP SCHEMA temp_func_test CASCADE;
-NOTICE: drop cascades to 41 other objects
+NOTICE: drop cascades to 45 other objects
DETAIL: drop cascades to function functest_a_1(text,date)
drop cascades to function functest_a_2(text[])
drop cascades to function functest_a_3()
@@ -872,6 +939,10 @@ drop cascades to function functest_s_3a()
drop cascades to function functest_s_10(text,date)
drop cascades to function functest_s_13()
drop cascades to function functest_s_15(integer)
+drop cascades to function functest_out_point(double precision,double precision)
+drop cascades to function functest_out_point2(double precision,double precision)
+drop cascades to function functest_out_point_sql(double precision,double precision)
+drop cascades to function functest_out_point2_sql(double precision,double precision)
drop cascades to function functest_b_2(bigint)
drop cascades to function functest_srf0()
drop cascades to function functest_sri1()
diff --git a/src/test/regress/sql/create_function_sql.sql b/src/test/regress/sql/create_function_sql.sql
index 4377c5b569c..4dd80bb8498 100644
--- a/src/test/regress/sql/create_function_sql.sql
+++ b/src/test/regress/sql/create_function_sql.sql
@@ -257,6 +257,38 @@ SELECT pg_get_functiondef('functest_S_16'::regproc);
DROP TABLE functest1 CASCADE;
+-- check cases with OUT parameters before IN ones
+
+CREATE FUNCTION functest_out_point(OUT p point, x float8, y float8)
+LANGUAGE sql AS $$ select point(x,y); $$;
+
+SELECT functest_out_point(11,22);
+
+CREATE FUNCTION functest_out_point2(OUT p point, float8, y float8)
+LANGUAGE sql AS $$ select point($1,y); $$;
+
+SELECT functest_out_point2(11,22);
+
+CREATE FUNCTION functest_out_point_sql(OUT p point, x float8, y float8)
+BEGIN ATOMIC SELECT point(x,y); END;
+
+SELECT functest_out_point_sql(11,22);
+
+SELECT pg_get_functiondef('functest_out_point_sql'::regproc);
+
+CREATE FUNCTION functest_out_point2_sql(OUT p point, float8, y float8)
+BEGIN ATOMIC SELECT point($1,y); END;
+
+SELECT functest_out_point2_sql(11,22);
+
+SELECT pg_get_functiondef('functest_out_point2_sql'::regproc);
+
+CREATE FUNCTION functest_out_error(OUT p point, x float8, y float8)
+LANGUAGE sql AS $$ select point(p,x); $$;
+
+CREATE FUNCTION functest_out_error_sql(OUT p point, x float8, y float8)
+BEGIN ATOMIC SELECT point(p,x); END;
+
-- test with views
CREATE TABLE functest3 (a int);
INSERT INTO functest3 VALUES (1), (2);
[parent: 30e70fa76faf]