From b140c8d7a3f3a5de4e4cc4a0b52909aa13060d4c Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sat, 22 Nov 2025 19:33:34 -0500 Subject: Add SupportRequestInlineInFrom planner support request. This request allows a support function to replace a function call appearing in FROM (typically a set-returning function) with an equivalent SELECT subquery. The subquery will then be subject to the planner's usual optimizations, potentially allowing a much better plan to be generated. While the planner has long done this automatically for simple SQL-language functions, it's now possible for extensions to do it for functions outside that group. Notably, this could be useful for functions that are presently implemented in PL/pgSQL and work by generating and then EXECUTE'ing a SQL query. Author: Paul A Jungwirth Reviewed-by: Tom Lane Discussion: https://postgr.es/m/09de6afa-c33d-4d94-a5cb-afc6cea0d2bb@illuminatedcomputing.com --- src/backend/optimizer/prep/prepjointree.c | 14 +- src/backend/optimizer/util/clauses.c | 309 +++++++++++++++++---------- src/include/nodes/supportnodes.h | 30 +++ src/include/optimizer/clauses.h | 4 +- src/test/regress/expected/misc_functions.out | 50 +++++ src/test/regress/regress.c | 121 +++++++++++ src/test/regress/sql/misc_functions.sql | 34 +++ src/tools/pgindent/typedefs.list | 3 +- 8 files changed, 440 insertions(+), 125 deletions(-) (limited to 'src') diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c index 481d8011791..7581695647d 100644 --- a/src/backend/optimizer/prep/prepjointree.c +++ b/src/backend/optimizer/prep/prepjointree.c @@ -1066,13 +1066,15 @@ pull_up_sublinks_qual_recurse(PlannerInfo *root, Node *node, /* * preprocess_function_rtes * Constant-simplify any FUNCTION RTEs in the FROM clause, and then - * attempt to "inline" any that are set-returning functions. + * attempt to "inline" any that can be converted to simple subqueries. * - * If an RTE_FUNCTION rtable entry invokes a set-returning function that + * If an RTE_FUNCTION rtable entry invokes a set-returning SQL function that * contains just a simple SELECT, we can convert the rtable entry to an - * RTE_SUBQUERY entry exposing the SELECT directly. This is especially - * useful if the subquery can then be "pulled up" for further optimization, - * but we do it even if not, to reduce executor overhead. + * RTE_SUBQUERY entry exposing the SELECT directly. Other sorts of functions + * are also inline-able if they have a support function that can generate + * the replacement sub-Query. This is especially useful if the subquery can + * then be "pulled up" for further optimization, but we do it even if not, + * to reduce executor overhead. * * This has to be done before we have started to do any optimization of * subqueries, else any such steps wouldn't get applied to subqueries @@ -1107,7 +1109,7 @@ preprocess_function_rtes(PlannerInfo *root) eval_const_expressions(root, (Node *) rte->functions); /* Check safety of expansion, and expand if possible */ - funcquery = inline_set_returning_function(root, rte); + funcquery = inline_function_in_from(root, rte); if (funcquery) { /* Successful expansion, convert the RTE to a subquery */ diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c index 81d768ff2a2..202ba8ed4bb 100644 --- a/src/backend/optimizer/util/clauses.c +++ b/src/backend/optimizer/util/clauses.c @@ -82,7 +82,7 @@ typedef struct int nargs; List *args; int sublevels_up; -} substitute_actual_srf_parameters_context; +} substitute_actual_parameters_in_from_context; typedef struct { @@ -154,10 +154,16 @@ static Node *substitute_actual_parameters(Node *expr, int nargs, List *args, static Node *substitute_actual_parameters_mutator(Node *node, substitute_actual_parameters_context *context); static void sql_inline_error_callback(void *arg); -static Query *substitute_actual_srf_parameters(Query *expr, - int nargs, List *args); -static Node *substitute_actual_srf_parameters_mutator(Node *node, - substitute_actual_srf_parameters_context *context); +static Query *inline_sql_function_in_from(PlannerInfo *root, + RangeTblFunction *rtfunc, + FuncExpr *fexpr, + HeapTuple func_tuple, + Form_pg_proc funcform, + const char *src); +static Query *substitute_actual_parameters_in_from(Query *expr, + int nargs, List *args); +static Node *substitute_actual_parameters_in_from_mutator(Node *node, + substitute_actual_parameters_in_from_context *context); static bool pull_paramids_walker(Node *node, Bitmapset **context); @@ -5149,50 +5155,42 @@ evaluate_expr(Expr *expr, Oid result_type, int32 result_typmod, /* - * inline_set_returning_function - * Attempt to "inline" a set-returning function in the FROM clause. + * inline_function_in_from + * Attempt to "inline" a function in the FROM clause. * * "rte" is an RTE_FUNCTION rangetable entry. If it represents a call of a - * set-returning SQL function that can safely be inlined, expand the function - * and return the substitute Query structure. Otherwise, return NULL. + * function that can be inlined, expand the function and return the + * substitute Query structure. Otherwise, return NULL. * * We assume that the RTE's expression has already been put through * eval_const_expressions(), which among other things will take care of * default arguments and named-argument notation. * * This has a good deal of similarity to inline_function(), but that's - * for the non-set-returning case, and there are enough differences to + * for the general-expression case, and there are enough differences to * justify separate functions. */ Query * -inline_set_returning_function(PlannerInfo *root, RangeTblEntry *rte) +inline_function_in_from(PlannerInfo *root, RangeTblEntry *rte) { RangeTblFunction *rtfunc; FuncExpr *fexpr; Oid func_oid; HeapTuple func_tuple; Form_pg_proc funcform; - char *src; - Datum tmp; - bool isNull; MemoryContext oldcxt; MemoryContext mycxt; + Datum tmp; + char *src; inline_error_callback_arg callback_arg; ErrorContextCallback sqlerrcontext; - SQLFunctionParseInfoPtr pinfo; - TypeFuncClass functypclass; - TupleDesc rettupdesc; - List *raw_parsetree_list; - List *querytree_list; - Query *querytree; + Query *querytree = NULL; Assert(rte->rtekind == RTE_FUNCTION); /* - * It doesn't make a lot of sense for a SQL SRF to refer to itself in its - * own FROM clause, since that must cause infinite recursion at runtime. - * It will cause this code to recurse too, so check for stack overflow. - * (There's no need to do more.) + * Guard against infinite recursion during expansion by checking for stack + * overflow. (There's no need to do more.) */ check_stack_depth(); @@ -5211,14 +5209,6 @@ inline_set_returning_function(PlannerInfo *root, RangeTblEntry *rte) func_oid = fexpr->funcid; - /* - * The function must be declared to return a set, else inlining would - * change the results if the contained SELECT didn't return exactly one - * row. - */ - if (!fexpr->funcretset) - return NULL; - /* * Refuse to inline if the arguments contain any volatile functions or * sub-selects. Volatile functions are rejected because inlining may @@ -5249,24 +5239,10 @@ inline_set_returning_function(PlannerInfo *root, RangeTblEntry *rte) funcform = (Form_pg_proc) GETSTRUCT(func_tuple); /* - * Forget it if the function is not SQL-language or has other showstopper - * properties. In particular it mustn't be declared STRICT, since we - * couldn't enforce that. It also mustn't be VOLATILE, because that is - * supposed to cause it to be executed with its own snapshot, rather than - * sharing the snapshot of the calling query. We also disallow returning - * SETOF VOID, because inlining would result in exposing the actual result - * of the function's last SELECT, which should not happen in that case. - * (Rechecking prokind, proretset, and pronargs is just paranoia.) + * If the function SETs any configuration parameters, inlining would cause + * us to miss making those changes. */ - if (funcform->prolang != SQLlanguageId || - funcform->prokind != PROKIND_FUNCTION || - funcform->proisstrict || - funcform->provolatile == PROVOLATILE_VOLATILE || - funcform->prorettype == VOIDOID || - funcform->prosecdef || - !funcform->proretset || - list_length(fexpr->args) != funcform->pronargs || - !heap_attisnull(func_tuple, Anum_pg_proc_proconfig, NULL)) + if (!heap_attisnull(func_tuple, Anum_pg_proc_proconfig, NULL)) { ReleaseSysCache(func_tuple); return NULL; @@ -5274,10 +5250,11 @@ inline_set_returning_function(PlannerInfo *root, RangeTblEntry *rte) /* * Make a temporary memory context, so that we don't leak all the stuff - * that parsing might create. + * that parsing and rewriting might create. If we succeed, we'll copy + * just the finished query tree back up to the caller's context. */ mycxt = AllocSetContextCreate(CurrentMemoryContext, - "inline_set_returning_function", + "inline_function_in_from", ALLOCSET_DEFAULT_SIZES); oldcxt = MemoryContextSwitchTo(mycxt); @@ -5285,9 +5262,30 @@ inline_set_returning_function(PlannerInfo *root, RangeTblEntry *rte) tmp = SysCacheGetAttrNotNull(PROCOID, func_tuple, Anum_pg_proc_prosrc); src = TextDatumGetCString(tmp); + /* + * If the function has an attached support function that can handle + * SupportRequestInlineInFrom, then attempt to inline with that. + */ + if (funcform->prosupport) + { + SupportRequestInlineInFrom req; + + req.type = T_SupportRequestInlineInFrom; + req.root = root; + req.rtfunc = rtfunc; + req.proc = func_tuple; + + querytree = (Query *) + DatumGetPointer(OidFunctionCall1(funcform->prosupport, + PointerGetDatum(&req))); + } + /* * Setup error traceback support for ereport(). This is so that we can - * finger the function that bad information came from. + * finger the function that bad information came from. We don't install + * this while running the support function, since it'd be likely to do the + * wrong thing: any parse errors reported during that are very likely not + * against the raw function source text. */ callback_arg.proname = NameStr(funcform->proname); callback_arg.prosrc = src; @@ -5297,33 +5295,158 @@ inline_set_returning_function(PlannerInfo *root, RangeTblEntry *rte) sqlerrcontext.previous = error_context_stack; error_context_stack = &sqlerrcontext; + /* + * If SupportRequestInlineInFrom didn't work, try our built-in inlining + * mechanism. + */ + if (!querytree) + querytree = inline_sql_function_in_from(root, rtfunc, fexpr, + func_tuple, funcform, src); + + if (!querytree) + goto fail; /* no luck there either, fail */ + + /* + * The result had better be a SELECT Query. + */ + Assert(IsA(querytree, Query)); + Assert(querytree->commandType == CMD_SELECT); + + /* + * Looks good --- substitute parameters into the query. + */ + querytree = substitute_actual_parameters_in_from(querytree, + funcform->pronargs, + fexpr->args); + + /* + * Copy the modified query out of the temporary memory context, and clean + * up. + */ + MemoryContextSwitchTo(oldcxt); + + querytree = copyObject(querytree); + + MemoryContextDelete(mycxt); + error_context_stack = sqlerrcontext.previous; + ReleaseSysCache(func_tuple); + + /* + * We don't have to fix collations here because the upper query is already + * parsed, ie, the collations in the RTE are what count. + */ + + /* + * Since there is now no trace of the function in the plan tree, we must + * explicitly record the plan's dependency on the function. + */ + record_plan_function_dependency(root, func_oid); + + /* + * We must also notice if the inserted query adds a dependency on the + * calling role due to RLS quals. + */ + if (querytree->hasRowSecurity) + root->glob->dependsOnRole = true; + + return querytree; + + /* Here if func is not inlinable: release temp memory and return NULL */ +fail: + MemoryContextSwitchTo(oldcxt); + MemoryContextDelete(mycxt); + error_context_stack = sqlerrcontext.previous; + ReleaseSysCache(func_tuple); + + return NULL; +} + +/* + * inline_sql_function_in_from + * + * This implements inline_function_in_from for SQL-language functions. + * Returns NULL if the function couldn't be inlined. + * + * The division of labor between here and inline_function_in_from is based + * on the rule that inline_function_in_from should make all checks that are + * certain to be required in both this case and the support-function case. + * Support functions might also want to make checks analogous to the ones + * made here, but then again they might not, or they might just assume that + * the function they are attached to can validly be inlined. + */ +static Query * +inline_sql_function_in_from(PlannerInfo *root, + RangeTblFunction *rtfunc, + FuncExpr *fexpr, + HeapTuple func_tuple, + Form_pg_proc funcform, + const char *src) +{ + Datum sqlbody; + bool isNull; + List *querytree_list; + Query *querytree; + TypeFuncClass functypclass; + TupleDesc rettupdesc; + + /* + * The function must be declared to return a set, else inlining would + * change the results if the contained SELECT didn't return exactly one + * row. + */ + if (!fexpr->funcretset) + return NULL; + + /* + * Forget it if the function is not SQL-language or has other showstopper + * properties. In particular it mustn't be declared STRICT, since we + * couldn't enforce that. It also mustn't be VOLATILE, because that is + * supposed to cause it to be executed with its own snapshot, rather than + * sharing the snapshot of the calling query. We also disallow returning + * SETOF VOID, because inlining would result in exposing the actual result + * of the function's last SELECT, which should not happen in that case. + * (Rechecking prokind, proretset, and pronargs is just paranoia.) + */ + if (funcform->prolang != SQLlanguageId || + funcform->prokind != PROKIND_FUNCTION || + funcform->proisstrict || + funcform->provolatile == PROVOLATILE_VOLATILE || + funcform->prorettype == VOIDOID || + funcform->prosecdef || + !funcform->proretset || + list_length(fexpr->args) != funcform->pronargs) + return NULL; + /* If we have prosqlbody, pay attention to that not prosrc */ - tmp = SysCacheGetAttr(PROCOID, - func_tuple, - Anum_pg_proc_prosqlbody, - &isNull); + sqlbody = SysCacheGetAttr(PROCOID, + func_tuple, + Anum_pg_proc_prosqlbody, + &isNull); if (!isNull) { Node *n; - n = stringToNode(TextDatumGetCString(tmp)); + n = stringToNode(TextDatumGetCString(sqlbody)); if (IsA(n, List)) querytree_list = linitial_node(List, castNode(List, n)); else querytree_list = list_make1(n); if (list_length(querytree_list) != 1) - goto fail; + return NULL; querytree = linitial(querytree_list); /* Acquire necessary locks, then apply rewriter. */ AcquireRewriteLocks(querytree, true, false); querytree_list = pg_rewrite_query(querytree); if (list_length(querytree_list) != 1) - goto fail; + return NULL; querytree = linitial(querytree_list); } else { + SQLFunctionParseInfoPtr pinfo; + List *raw_parsetree_list; + /* * Set up to handle parameters while parsing the function body. We * can use the FuncExpr just created as the input for @@ -5340,14 +5463,14 @@ inline_set_returning_function(PlannerInfo *root, RangeTblEntry *rte) */ raw_parsetree_list = pg_parse_query(src); if (list_length(raw_parsetree_list) != 1) - goto fail; + return NULL; querytree_list = pg_analyze_and_rewrite_withcb(linitial(raw_parsetree_list), src, (ParserSetupHook) sql_fn_parser_setup, pinfo, NULL); if (list_length(querytree_list) != 1) - goto fail; + return NULL; querytree = linitial(querytree_list); } @@ -5372,7 +5495,7 @@ inline_set_returning_function(PlannerInfo *root, RangeTblEntry *rte) */ if (!IsA(querytree, Query) || querytree->commandType != CMD_SELECT) - goto fail; + return NULL; /* * Make sure the function (still) returns what it's declared to. This @@ -5394,7 +5517,7 @@ inline_set_returning_function(PlannerInfo *root, RangeTblEntry *rte) (functypclass == TYPEFUNC_COMPOSITE || functypclass == TYPEFUNC_COMPOSITE_DOMAIN || functypclass == TYPEFUNC_RECORD)) - goto fail; /* reject not-whole-tuple-result cases */ + return NULL; /* reject not-whole-tuple-result cases */ /* * check_sql_fn_retval might've inserted a projection step, but that's @@ -5402,53 +5525,7 @@ inline_set_returning_function(PlannerInfo *root, RangeTblEntry *rte) */ querytree = linitial_node(Query, querytree_list); - /* - * Looks good --- substitute parameters into the query. - */ - querytree = substitute_actual_srf_parameters(querytree, - funcform->pronargs, - fexpr->args); - - /* - * Copy the modified query out of the temporary memory context, and clean - * up. - */ - MemoryContextSwitchTo(oldcxt); - - querytree = copyObject(querytree); - - MemoryContextDelete(mycxt); - error_context_stack = sqlerrcontext.previous; - ReleaseSysCache(func_tuple); - - /* - * We don't have to fix collations here because the upper query is already - * parsed, ie, the collations in the RTE are what count. - */ - - /* - * Since there is now no trace of the function in the plan tree, we must - * explicitly record the plan's dependency on the function. - */ - record_plan_function_dependency(root, func_oid); - - /* - * We must also notice if the inserted query adds a dependency on the - * calling role due to RLS quals. - */ - if (querytree->hasRowSecurity) - root->glob->dependsOnRole = true; - return querytree; - - /* Here if func is not inlinable: release temp memory and return NULL */ -fail: - MemoryContextSwitchTo(oldcxt); - MemoryContextDelete(mycxt); - error_context_stack = sqlerrcontext.previous; - ReleaseSysCache(func_tuple); - - return NULL; } /* @@ -5458,23 +5535,23 @@ fail: * that it needs its own code. */ static Query * -substitute_actual_srf_parameters(Query *expr, int nargs, List *args) +substitute_actual_parameters_in_from(Query *expr, int nargs, List *args) { - substitute_actual_srf_parameters_context context; + substitute_actual_parameters_in_from_context context; context.nargs = nargs; context.args = args; context.sublevels_up = 1; return query_tree_mutator(expr, - substitute_actual_srf_parameters_mutator, + substitute_actual_parameters_in_from_mutator, &context, 0); } static Node * -substitute_actual_srf_parameters_mutator(Node *node, - substitute_actual_srf_parameters_context *context) +substitute_actual_parameters_in_from_mutator(Node *node, + substitute_actual_parameters_in_from_context *context) { Node *result; @@ -5484,7 +5561,7 @@ substitute_actual_srf_parameters_mutator(Node *node, { context->sublevels_up++; result = (Node *) query_tree_mutator((Query *) node, - substitute_actual_srf_parameters_mutator, + substitute_actual_parameters_in_from_mutator, context, 0); context->sublevels_up--; @@ -5509,7 +5586,7 @@ substitute_actual_srf_parameters_mutator(Node *node, } } return expression_tree_mutator(node, - substitute_actual_srf_parameters_mutator, + substitute_actual_parameters_in_from_mutator, context); } diff --git a/src/include/nodes/supportnodes.h b/src/include/nodes/supportnodes.h index 7b623d54058..ea774c7ef6a 100644 --- a/src/include/nodes/supportnodes.h +++ b/src/include/nodes/supportnodes.h @@ -39,6 +39,8 @@ typedef struct PlannerInfo PlannerInfo; /* avoid including pathnodes.h here */ typedef struct IndexOptInfo IndexOptInfo; typedef struct SpecialJoinInfo SpecialJoinInfo; typedef struct WindowClause WindowClause; +typedef struct RangeTblFunction RangeTblFunction; /* ditto for parsenodes.h */ +typedef struct HeapTupleData *HeapTuple; /* and htup.h too */ /* * The Simplify request allows the support function to perform plan-time @@ -69,6 +71,34 @@ typedef struct SupportRequestSimplify FuncExpr *fcall; /* Function call to be simplified */ } SupportRequestSimplify; +/* + * The InlineInFrom request allows the support function to perform plan-time + * simplification of a call to its target function that appears in FROM. + * The rules for this are sufficiently different from ordinary expressions + * that it's best to make this a separate request from Simplify. + * + * The planner's PlannerInfo "root" is typically not needed, but can be + * consulted if it's necessary to obtain info about Vars present in + * the given node tree. Beware that root could be NULL in some usages. + * + * "rtfunc" will be a RangeTblFunction node for the support function's target + * function. The call appeared alone (and without ORDINALITY) in FROM. + * + * "proc" will be the HeapTuple for the pg_proc row of the target function. + * + * The result should be a semantically-equivalent SELECT Query tree, + * or NULL if no simplification could be performed. The tree must have + * been passed through parse analysis and rewrite. + */ +typedef struct SupportRequestInlineInFrom +{ + NodeTag type; + + PlannerInfo *root; /* Planner's infrastructure */ + RangeTblFunction *rtfunc; /* Function call to be simplified */ + HeapTuple proc; /* Function definition from pg_proc */ +} SupportRequestInlineInFrom; + /* * The Selectivity request allows the support function to provide a * selectivity estimate for a function appearing at top level of a WHERE diff --git a/src/include/optimizer/clauses.h b/src/include/optimizer/clauses.h index 0dffec00ede..fc38eae5c5a 100644 --- a/src/include/optimizer/clauses.h +++ b/src/include/optimizer/clauses.h @@ -50,8 +50,8 @@ extern int NumRelids(PlannerInfo *root, Node *clause); extern void CommuteOpExpr(OpExpr *clause); -extern Query *inline_set_returning_function(PlannerInfo *root, - RangeTblEntry *rte); +extern Query *inline_function_in_from(PlannerInfo *root, + RangeTblEntry *rte); extern Bitmapset *pull_paramids(Expr *expr); diff --git a/src/test/regress/expected/misc_functions.out b/src/test/regress/expected/misc_functions.out index e76e28b95ce..d7d965d884a 100644 --- a/src/test/regress/expected/misc_functions.out +++ b/src/test/regress/expected/misc_functions.out @@ -808,6 +808,56 @@ false, true, false, true); Function Scan on generate_series g (cost=N..N rows=1000 width=N) (1 row) +-- +-- Test SupportRequestInlineInFrom request +-- +CREATE FUNCTION test_inline_in_from_support_func(internal) + RETURNS internal + AS :'regresslib', 'test_inline_in_from_support_func' + LANGUAGE C STRICT; +CREATE FUNCTION foo_from_bar(colname TEXT, tablename TEXT, filter TEXT) +RETURNS SETOF TEXT +LANGUAGE plpgsql +AS $function$ +DECLARE + sql TEXT; +BEGIN + sql := format('SELECT %I::text FROM %I', colname, tablename); + IF filter IS NOT NULL THEN + sql := CONCAT(sql, format(' WHERE %I::text = $1', colname)); + END IF; + RETURN QUERY EXECUTE sql USING filter; +END; +$function$ STABLE; +ALTER FUNCTION foo_from_bar(TEXT, TEXT, TEXT) + SUPPORT test_inline_in_from_support_func; +SELECT * FROM foo_from_bar('f1', 'text_tbl', NULL); + foo_from_bar +------------------- + doh! + hi de ho neighbor +(2 rows) + +SELECT * FROM foo_from_bar('f1', 'text_tbl', 'doh!'); + foo_from_bar +-------------- + doh! +(1 row) + +EXPLAIN (COSTS OFF) SELECT * FROM foo_from_bar('f1', 'text_tbl', NULL); + QUERY PLAN +---------------------- + Seq Scan on text_tbl +(1 row) + +EXPLAIN (COSTS OFF) SELECT * FROM foo_from_bar('f1', 'text_tbl', 'doh!'); + QUERY PLAN +------------------------------- + Seq Scan on text_tbl + Filter: (f1 = 'doh!'::text) +(2 rows) + +DROP FUNCTION foo_from_bar; -- Test functions for control data SELECT count(*) > 0 AS ok FROM pg_control_checkpoint(); ok diff --git a/src/test/regress/regress.c b/src/test/regress/regress.c index a2db6080876..56cc0567b1c 100644 --- a/src/test/regress/regress.c +++ b/src/test/regress/regress.c @@ -28,6 +28,7 @@ #include "commands/sequence.h" #include "commands/trigger.h" #include "executor/executor.h" +#include "executor/functions.h" #include "executor/spi.h" #include "funcapi.h" #include "mb/pg_wchar.h" @@ -39,6 +40,7 @@ #include "port/atomics.h" #include "postmaster/postmaster.h" /* for MAX_BACKENDS */ #include "storage/spin.h" +#include "tcop/tcopprot.h" #include "utils/array.h" #include "utils/builtins.h" #include "utils/geo_decls.h" @@ -803,6 +805,125 @@ test_support_func(PG_FUNCTION_ARGS) PG_RETURN_POINTER(ret); } +PG_FUNCTION_INFO_V1(test_inline_in_from_support_func); +Datum +test_inline_in_from_support_func(PG_FUNCTION_ARGS) +{ + Node *rawreq = (Node *) PG_GETARG_POINTER(0); + + if (IsA(rawreq, SupportRequestInlineInFrom)) + { + /* + * Assume that the target is foo_from_bar; that's safe as long as we + * don't attach this to any other function. + */ + SupportRequestInlineInFrom *req = (SupportRequestInlineInFrom *) rawreq; + StringInfoData sql; + RangeTblFunction *rtfunc = req->rtfunc; + FuncExpr *expr = (FuncExpr *) rtfunc->funcexpr; + Node *node; + Const *c; + char *colname; + char *tablename; + SQLFunctionParseInfoPtr pinfo; + List *raw_parsetree_list; + List *querytree_list; + Query *querytree; + + if (list_length(expr->args) != 3) + { + ereport(WARNING, (errmsg("test_inline_in_from_support_func called with %d args but expected 3", list_length(expr->args)))); + PG_RETURN_POINTER(NULL); + } + + /* Get colname */ + node = linitial(expr->args); + if (!IsA(node, Const)) + { + ereport(WARNING, (errmsg("test_inline_in_from_support_func called with non-Const parameters"))); + PG_RETURN_POINTER(NULL); + } + + c = (Const *) node; + if (c->consttype != TEXTOID || c->constisnull) + { + ereport(WARNING, (errmsg("test_inline_in_from_support_func called with non-TEXT parameters"))); + PG_RETURN_POINTER(NULL); + } + colname = TextDatumGetCString(c->constvalue); + + /* Get tablename */ + node = lsecond(expr->args); + if (!IsA(node, Const)) + { + ereport(WARNING, (errmsg("test_inline_in_from_support_func called with non-Const parameters"))); + PG_RETURN_POINTER(NULL); + } + + c = (Const *) node; + if (c->consttype != TEXTOID || c->constisnull) + { + ereport(WARNING, (errmsg("test_inline_in_from_support_func called with non-TEXT parameters"))); + PG_RETURN_POINTER(NULL); + } + tablename = TextDatumGetCString(c->constvalue); + + /* Begin constructing replacement SELECT query. */ + initStringInfo(&sql); + appendStringInfo(&sql, "SELECT %s::text FROM %s", + quote_identifier(colname), + quote_identifier(tablename)); + + /* Add filter expression if present. */ + node = lthird(expr->args); + if (!(IsA(node, Const) && ((Const *) node)->constisnull)) + { + /* + * We only filter if $3 is not constant-NULL. This is not a very + * exact implementation of the PL/pgSQL original, but it's close + * enough for demonstration purposes. + */ + appendStringInfo(&sql, " WHERE %s::text = $3", + quote_identifier(colname)); + } + + /* Build a SQLFunctionParseInfo with the parameters of my function. */ + pinfo = prepare_sql_fn_parse_info(req->proc, + (Node *) expr, + expr->inputcollid); + + /* Parse the generated SQL. */ + raw_parsetree_list = pg_parse_query(sql.data); + if (list_length(raw_parsetree_list) != 1) + { + ereport(WARNING, (errmsg("test_inline_in_from_support_func parsed to more than one node"))); + PG_RETURN_POINTER(NULL); + } + + /* Analyze the parse tree as if it were a SQL-language body. */ + querytree_list = pg_analyze_and_rewrite_withcb(linitial(raw_parsetree_list), + sql.data, + (ParserSetupHook) sql_fn_parser_setup, + pinfo, NULL); + if (list_length(querytree_list) != 1) + { + ereport(WARNING, (errmsg("test_inline_in_from_support_func rewrote to more than one node"))); + PG_RETURN_POINTER(NULL); + } + + querytree = linitial(querytree_list); + if (!IsA(querytree, Query)) + { + ereport(WARNING, (errmsg("test_inline_in_from_support_func didn't parse to a Query"))); + PG_RETURN_POINTER(NULL); + } + + PG_RETURN_POINTER(querytree); + } + + PG_RETURN_POINTER(NULL); +} + PG_FUNCTION_INFO_V1(test_opclass_options_func); Datum test_opclass_options_func(PG_FUNCTION_ARGS) diff --git a/src/test/regress/sql/misc_functions.sql b/src/test/regress/sql/misc_functions.sql index 220472d5ad1..0fc20fbb6b4 100644 --- a/src/test/regress/sql/misc_functions.sql +++ b/src/test/regress/sql/misc_functions.sql @@ -360,6 +360,40 @@ SELECT explain_mask_costs($$ SELECT * FROM generate_series(25.0, 2.0, 0.0) g(s);$$, false, true, false, true); +-- +-- Test SupportRequestInlineInFrom request +-- + +CREATE FUNCTION test_inline_in_from_support_func(internal) + RETURNS internal + AS :'regresslib', 'test_inline_in_from_support_func' + LANGUAGE C STRICT; + +CREATE FUNCTION foo_from_bar(colname TEXT, tablename TEXT, filter TEXT) +RETURNS SETOF TEXT +LANGUAGE plpgsql +AS $function$ +DECLARE + sql TEXT; +BEGIN + sql := format('SELECT %I::text FROM %I', colname, tablename); + IF filter IS NOT NULL THEN + sql := CONCAT(sql, format(' WHERE %I::text = $1', colname)); + END IF; + RETURN QUERY EXECUTE sql USING filter; +END; +$function$ STABLE; + +ALTER FUNCTION foo_from_bar(TEXT, TEXT, TEXT) + SUPPORT test_inline_in_from_support_func; + +SELECT * FROM foo_from_bar('f1', 'text_tbl', NULL); +SELECT * FROM foo_from_bar('f1', 'text_tbl', 'doh!'); +EXPLAIN (COSTS OFF) SELECT * FROM foo_from_bar('f1', 'text_tbl', NULL); +EXPLAIN (COSTS OFF) SELECT * FROM foo_from_bar('f1', 'text_tbl', 'doh!'); + +DROP FUNCTION foo_from_bar; + -- Test functions for control data SELECT count(*) > 0 AS ok FROM pg_control_checkpoint(); SELECT count(*) > 0 AS ok FROM pg_control_init(); diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index 27a4d131897..0d1ea4ec63d 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -2917,6 +2917,7 @@ SubscriptionRelState SummarizerReadLocalXLogPrivate SupportRequestCost SupportRequestIndexCondition +SupportRequestInlineInFrom SupportRequestModifyInPlace SupportRequestOptimizeWindowClause SupportRequestRows @@ -4140,7 +4141,7 @@ storeRes_func stream_stop_callback string substitute_actual_parameters_context -substitute_actual_srf_parameters_context +substitute_actual_parameters_in_from_context substitute_grouped_columns_context substitute_phv_relids_context subxids_array_status -- cgit v1.2.3