postgres.git / summary / log / commit / refs

commit    c87192d13dcd933256b1a2858bc4ddf2c1eb56d7
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 28c38fdbcb5..7b352b6facc 100644 --- a/src/backend/commands/functioncmds.c +++ b/src/backend/commands/functioncmds.c @@ -311,6 +311,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 */ @@ -404,9 +407,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; @@ -912,9 +912,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 1628abbca2e..2d3625c9b70 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -180,8 +180,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; /* @@ -3525,19 +3525,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 1c4c5f7cc13..4e114c31e96 100644 --- a/src/backend/utils/fmgr/funcapi.c +++ b/src/backend/utils/fmgr/funcapi.c @@ -1372,6 +1372,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. */ @@ -1516,7 +1519,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 ba7786d31c3..dfb0650341f 100644 --- a/src/pl/plpython/plpy_procedure.c +++ b/src/pl/plpython/plpy_procedure.c @@ -314,7 +314,8 @@ PLy_procedure_create(HeapTuple procTup, Oid fn_oid, bool is_trigger) 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 40c462e84e0..7d49d1c8c5f 100644 --- a/src/test/modules/plsample/plsample.c +++ b/src/test/modules/plsample/plsample.c @@ -110,6 +110,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, @@ -148,23 +149,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 660bd556bc2..d9be7723607 100644 --- a/src/test/regress/expected/create_function_sql.out +++ b/src/test/regress/expected/create_function_sql.out @@ -484,6 +484,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); @@ -754,7 +821,7 @@ CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL ERROR: only one AS item needed for language "sql" -- Cleanup DROP SCHEMA temp_func_test CASCADE; -NOTICE: drop cascades to 33 other objects +NOTICE: drop cascades to 37 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() @@ -780,6 +847,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_sri1() drop cascades to function voidtest1(integer) diff --git a/src/test/regress/sql/create_function_sql.sql b/src/test/regress/sql/create_function_sql.sql index c8547b656a7..8e5df16a5ba 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: 179f22f256ab]