public inbox for [email protected]
help / color / mirror / Atom feedFrom: Paul A Jungwirth <[email protected]>
To: Tom Lane <[email protected]>
Cc: Heikki Linnakangas <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: Inline non-SQL SRFs using SupportRequestSimplify
Date: Fri, 8 Aug 2025 14:46:01 -0700
Message-ID: <CA+renyXxanvX8rnrqTNkZt1r9=rm59MHSG4DDVfsV2aUGe3dUQ@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
On Mon, Jul 14, 2025 at 2:21 PM Tom Lane <[email protected]> wrote:
> I got around to looking at this again. I generally agree with your
> approach to the refactoring in clauses.c, with minor nitpicks:
Thanks for taking another look! Revisions attached.
> * I don't like postponing the early exit for its-not-a-SELECT;
> as coded, this wastes a pretty decent number of cycles transforming
> a querytree that won't be used (not to mention that I'm not sure
> that our usage of check_sql_fn_retval won't fail on a non-SELECT).
> So I think we should keep this bit where it is:
>
> - /*
> - * The single command must be a plain SELECT.
> - */
> - if (!IsA(querytree, Query) ||
> - querytree->commandType != CMD_SELECT)
> - goto fail;
>
> and then in the other path, simply Assert that those two conditions
> hold for anything the support function might try to give back.
Okay.
> * I'm inclined to think that the test for "it must be declared to
> return a set" should stay in inline_sql_set_returning_function.
> In the case of a support-function-supported function, it's okay either
> if the function returns a set or if it is guaranteed to return exactly
> one row (including edge cases such as null function arguments).
> The support function either knows that already or can take the
> responsibility for checking it. But if we do it like this, we
> foreclose the possibility of supporting the latter class of functions.
Makes sense. Done.
> * But on the other hand, I wonder if this bit shouldn't move to
> the outer function:
>
> /*
> * Refuse to inline if the arguments contain any volatile functions or
> * sub-selects. Volatile functions are rejected because inlining may
> * result in the arguments being evaluated multiple times, risking a
> * change in behavior. Sub-selects are rejected partly for implementation
> * reasons (pushing them down another level might change their behavior)
> * and partly because they're likely to be expensive and so multiple
> * evaluation would be bad.
> */
> if (contain_volatile_functions((Node *) fexpr->args) ||
> contain_subplans((Node *) fexpr->args))
> return NULL;
>
> I am not really convinced that any support function could safely
> ignore those restrictions, and I do fear that a lot would omit the
> enforcement and thereby produce wrong queries in such cases. Another
> thing that likely needs to be in the outer wrapper is the check that
> pg_proc_proconfig is empty, because that doesn't seem like a case
> that support functions could skip over either.
Agreed, done.
> * I don't like the memory management. I think creation/destruction
> of the temp context should occur at the outer level, and in particular
> that we want to perform substitute_actual_srf_parameters() while still
> working in the temp context, and copy out only the final form of the
> query tree. This addresses your XXX comment in v2-0002, and also
> saves support functions from having to re-invent that wheel.
Okay, done. I was trying to defer creating a memory context past as
many checks as possible. It's not an expensive thing to do?
> > I didn't love passing a SysCache HeapTuple into another function,
>
> No, that's perfectly common; see for example
> prepare_sql_fn_parse_info. In fact, one thing I don't like in v2-0002
> is that you should pass the pg_proc entry to the support function as a
> HeapTuple not Form_pg_proc. It's possible to get the Form_pg_proc
> pointer from the HeapTuple but not vice versa, while the Form_pg_proc
> does not allow access to varlena fields, which makes it useless for
> many cases. Even your own example function is forced to re-fetch
> the syscache entry because of this.
Okay, thanks for explaining! Done.
> One other comment on v2-0002 is that this bit doesn't look right:
>
> + /* Get filter if present */
> + node = lthird(expr->args);
> + if (!(IsA(node, Const) && ((Const *) node)->constisnull))
> + {
> + appendStringInfo(&sql, " WHERE %s::text = $3", quote_identifier(colname));
> + }
>
> It's not actually doing anything with the "node" value.
This is correct, but I added a comment. The idea is that $3 will get
the value of "node".
> Backing up to a higher level, it seems like we still have no answer
> for how to build a valid support function result besides "construct an
> equivalent SQL query string and feed it through parse analysis and
> rewrite". That seems both restrictive and expensive. In particular
> it begs the question of why the target function couldn't just have
> been written as a SQL function to begin with. So I still have kind
> of a low estimate of this feature's usefulness. Is there a way to
> do better?
Parsing the function body is no more expensive than what we'd do to
execute it separately, right? And by inlining we only do it once. But
if it was too much for someone, perhaps they could keep a cache of
node trees based on the function arguments and/or hash of the SQL
string, not unlike how foreign keys cache query plans. Or they could
build the node tree directly, without parsing. But parsing the
equivalent string is easy and covers most uses. I can even imagine a
way to eventually semi-automate it, e.g. creating a general-purpose
support function that you can attach to (many) PL/pgSQL functions that
end in `EXECUTE format(...)`.
The reason for supporting more than SQL functions is to let you
construct the query dynamically, e.g. with user-supplied table/column
names, or to only include some expensive filters if needed. This would
be great for building functions that implement temporal
outer/semi/antijoin. Another use-case I personally have, which I think
is quite common, is building "parameterized views" for permissions
checks, e.g. visible_sales(user). In that case we may only need to
include certain joins if the user belongs to certain roles (e.g. a
third-party sales rep).
Rebased to 04b7ff3cd3.
Yours,
--
Paul ~{:-)
[email protected]
Attachments:
[application/octet-stream] v3-0001-Move-some-things-outside-of-inline_set_returning_.patch (12.9K, ../CA+renyXxanvX8rnrqTNkZt1r9=rm59MHSG4DDVfsV2aUGe3dUQ@mail.gmail.com/2-v3-0001-Move-some-things-outside-of-inline_set_returning_.patch)
download | inline diff:
From c74e3fea09f44df39c62299777527e28616e0345 Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <[email protected]>
Date: Tue, 24 Jun 2025 19:10:15 -0700
Subject: [PATCH v3 1/2] Move some things outside of
inline_set_returning_function.
Added a new inline_sql_set_returning_function in preparation for
inline_set_returning_function_with_support. Then
inline_set_returning_function can call both, handling their shared needs
itself.
Author: Paul A. Jungwirth <[email protected]>
---
src/backend/optimizer/util/clauses.c | 273 +++++++++++++++------------
1 file changed, 157 insertions(+), 116 deletions(-)
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index 6f0b338d2cd..77f48ff4069 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -5146,36 +5146,21 @@ 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.
- *
- * "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.
+ * inline_sql_set_returning_function
*
- * 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 implements inline_set_returning_function for sql-language functions.
+ * It parses the body (or uses the pre-parsed body if available).
*
- * 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
- * justify separate functions.
+ * Returns NULL if the function couldn't be inlined.
*/
-Query *
-inline_set_returning_function(PlannerInfo *root, RangeTblEntry *rte)
+static Query *
+inline_sql_set_returning_function(PlannerInfo *root, RangeTblEntry *rte,
+ RangeTblFunction *rtfunc,
+ FuncExpr *fexpr, Oid func_oid, HeapTuple func_tuple,
+ Form_pg_proc funcform, char *src)
{
- RangeTblFunction *rtfunc;
- FuncExpr *fexpr;
- Oid func_oid;
- HeapTuple func_tuple;
- Form_pg_proc funcform;
- char *src;
- Datum tmp;
+ Datum sqlbody;
bool isNull;
- MemoryContext oldcxt;
- MemoryContext mycxt;
- inline_error_callback_arg callback_arg;
- ErrorContextCallback sqlerrcontext;
SQLFunctionParseInfoPtr pinfo;
TypeFuncClass functypclass;
TupleDesc rettupdesc;
@@ -5185,29 +5170,6 @@ inline_set_returning_function(PlannerInfo *root, RangeTblEntry *rte)
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.)
- */
- check_stack_depth();
-
- /* Fail if the RTE has ORDINALITY - we don't implement that here. */
- if (rte->funcordinality)
- return NULL;
-
- /* Fail if RTE isn't a single, simple FuncExpr */
- if (list_length(rte->functions) != 1)
- return NULL;
- rtfunc = (RangeTblFunction *) linitial(rte->functions);
-
- if (!IsA(rtfunc->funcexpr, FuncExpr))
- return NULL;
- fexpr = (FuncExpr *) rtfunc->funcexpr;
-
- 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
@@ -5216,35 +5178,6 @@ inline_set_returning_function(PlannerInfo *root, RangeTblEntry *rte)
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
- * result in the arguments being evaluated multiple times, risking a
- * change in behavior. Sub-selects are rejected partly for implementation
- * reasons (pushing them down another level might change their behavior)
- * and partly because they're likely to be expensive and so multiple
- * evaluation would be bad.
- */
- if (contain_volatile_functions((Node *) fexpr->args) ||
- contain_subplans((Node *) fexpr->args))
- return NULL;
-
- /* Check permission to call function (fail later, if not) */
- if (object_aclcheck(ProcedureRelationId, func_oid, GetUserId(), ACL_EXECUTE) != ACLCHECK_OK)
- return NULL;
-
- /* Check whether a plugin wants to hook function entry/exit */
- if (FmgrHookIsNeeded(func_oid))
- return NULL;
-
- /*
- * OK, let's take a look at the function's pg_proc entry.
- */
- func_tuple = SearchSysCache1(PROCOID, ObjectIdGetDatum(func_oid));
- if (!HeapTupleIsValid(func_tuple))
- elog(ERROR, "cache lookup failed for function %u", func_oid);
- 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
@@ -5262,61 +5195,34 @@ inline_set_returning_function(PlannerInfo *root, RangeTblEntry *rte)
funcform->prorettype == VOIDOID ||
funcform->prosecdef ||
!funcform->proretset ||
- list_length(fexpr->args) != funcform->pronargs ||
- !heap_attisnull(func_tuple, Anum_pg_proc_proconfig, NULL))
+ list_length(fexpr->args) != funcform->pronargs)
{
- ReleaseSysCache(func_tuple);
return NULL;
}
- /*
- * Make a temporary memory context, so that we don't leak all the stuff
- * that parsing might create.
- */
- mycxt = AllocSetContextCreate(CurrentMemoryContext,
- "inline_set_returning_function",
- ALLOCSET_DEFAULT_SIZES);
- oldcxt = MemoryContextSwitchTo(mycxt);
-
- /* Fetch the function body */
- tmp = SysCacheGetAttrNotNull(PROCOID, func_tuple, Anum_pg_proc_prosrc);
- src = TextDatumGetCString(tmp);
-
- /*
- * Setup error traceback support for ereport(). This is so that we can
- * finger the function that bad information came from.
- */
- callback_arg.proname = NameStr(funcform->proname);
- callback_arg.prosrc = src;
-
- sqlerrcontext.callback = sql_inline_error_callback;
- sqlerrcontext.arg = &callback_arg;
- sqlerrcontext.previous = error_context_stack;
- error_context_stack = &sqlerrcontext;
-
/* 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
@@ -5337,14 +5243,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);
}
@@ -5369,7 +5275,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
@@ -5391,7 +5297,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
@@ -5399,6 +5305,141 @@ inline_set_returning_function(PlannerInfo *root, RangeTblEntry *rte)
*/
querytree = linitial_node(Query, querytree_list);
+ return querytree;
+}
+
+/*
+ * inline_set_returning_function
+ * Attempt to "inline" an SQL set-returning 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.
+ *
+ * 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
+ * justify separate functions.
+ *
+ * It allocates its own temporary MemoryContext for the parsing, then copies
+ * the result into the caller's context.
+ */
+Query *
+inline_set_returning_function(PlannerInfo *root, RangeTblEntry *rte)
+{
+ RangeTblFunction *rtfunc;
+ FuncExpr *fexpr;
+ Oid func_oid;
+ HeapTuple func_tuple;
+ Form_pg_proc funcform;
+ Datum tmp;
+ char *src;
+ inline_error_callback_arg callback_arg;
+ ErrorContextCallback sqlerrcontext;
+ MemoryContext oldcxt;
+ MemoryContext mycxt;
+ Query *querytree;
+
+ Assert(rte->rtekind == RTE_FUNCTION);
+
+ /*
+ * It doesn't make a lot of sense for a 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.)
+ */
+ check_stack_depth();
+
+ /* Fail if the RTE has ORDINALITY - we don't implement that here. */
+ if (rte->funcordinality)
+ return NULL;
+
+ /* Fail if RTE isn't a single, simple FuncExpr */
+ if (list_length(rte->functions) != 1)
+ return NULL;
+ rtfunc = (RangeTblFunction *) linitial(rte->functions);
+
+ if (!IsA(rtfunc->funcexpr, FuncExpr))
+ return NULL;
+ fexpr = (FuncExpr *) rtfunc->funcexpr;
+
+ func_oid = fexpr->funcid;
+
+ /*
+ * Refuse to inline if the arguments contain any volatile functions or
+ * sub-selects. Volatile functions are rejected because inlining may
+ * result in the arguments being evaluated multiple times, risking a
+ * change in behavior. Sub-selects are rejected partly for implementation
+ * reasons (pushing them down another level might change their behavior)
+ * and partly because they're likely to be expensive and so multiple
+ * evaluation would be bad.
+ */
+ if (contain_volatile_functions((Node *) fexpr->args) ||
+ contain_subplans((Node *) fexpr->args))
+ return NULL;
+
+ /* Check permission to call function (fail later, if not) */
+ if (object_aclcheck(ProcedureRelationId, func_oid, GetUserId(), ACL_EXECUTE) != ACLCHECK_OK)
+ return NULL;
+
+ /* Check whether a plugin wants to hook function entry/exit */
+ if (FmgrHookIsNeeded(func_oid))
+ return NULL;
+
+ /*
+ * OK, let's take a look at the function's pg_proc entry.
+ */
+ func_tuple = SearchSysCache1(PROCOID, ObjectIdGetDatum(func_oid));
+ if (!HeapTupleIsValid(func_tuple))
+ elog(ERROR, "cache lookup failed for function %u", func_oid);
+ funcform = (Form_pg_proc) GETSTRUCT(func_tuple);
+
+ /*
+ * Make a temporary memory context, so that we don't leak all the stuff
+ * that parsing might create.
+ */
+ mycxt = AllocSetContextCreate(CurrentMemoryContext,
+ "inline_set_returning_function",
+ ALLOCSET_DEFAULT_SIZES);
+ oldcxt = MemoryContextSwitchTo(mycxt);
+
+ /* Fetch the function body */
+ tmp = SysCacheGetAttrNotNull(PROCOID, func_tuple, Anum_pg_proc_prosrc);
+ src = TextDatumGetCString(tmp);
+
+ /*
+ * Setup error traceback support for ereport(). This is so that we can
+ * finger the function that bad information came from.
+ */
+ callback_arg.proname = NameStr(funcform->proname);
+ callback_arg.prosrc = src;
+
+ sqlerrcontext.callback = sql_inline_error_callback;
+ sqlerrcontext.arg = &callback_arg;
+ sqlerrcontext.previous = error_context_stack;
+ error_context_stack = &sqlerrcontext;
+
+ /*
+ * If the function SETs configuration parameters, inlining would cause us
+ * to skip those changes.
+ */
+ if (!heap_attisnull(func_tuple, Anum_pg_proc_proconfig, NULL))
+ goto fail;
+
+ querytree = inline_sql_set_returning_function(root, rte, rtfunc, fexpr,
+ func_oid, func_tuple, funcform,
+ src);
+
+ if (!querytree)
+ goto fail;
+
+ /* Only SELECTs are permitted */
+ Assert(IsA(querytree, Query));
+ Assert(querytree->commandType == CMD_SELECT);
+
/*
* Looks good --- substitute parameters into the query.
*/
--
2.45.0
[application/octet-stream] v3-0002-Add-SupportRequestInlineSRF.patch (27.5K, ../CA+renyXxanvX8rnrqTNkZt1r9=rm59MHSG4DDVfsV2aUGe3dUQ@mail.gmail.com/3-v3-0002-Add-SupportRequestInlineSRF.patch)
download | inline diff:
From d6409f6d61019e54ae5c1a49ef57b7bded80de7b Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <[email protected]>
Date: Tue, 24 Jun 2025 21:30:02 -0700
Subject: [PATCH v3 2/2] Add SupportRequestInlineSRF
If a set-returning function has an attached support function that can
handle SupportRequestInlineSRF, then we replace the FuncExpr with a
Query node built by the support function. Then the planner can rewrite
the Query as if it were from a SQL-language function, merging it with
the outer query.
Author: Paul A. Jungwirth <[email protected]>
---
doc/src/sgml/xfunc.sgml | 101 +++++++++
src/backend/optimizer/util/clauses.c | 63 +++++-
src/include/nodes/supportnodes.h | 34 +++
src/test/regress/expected/misc_functions.out | 212 +++++++++++++++++++
src/test/regress/regress.c | 114 ++++++++++
src/test/regress/sql/misc_functions.sql | 86 ++++++++
src/tools/pgindent/typedefs.list | 1 +
7 files changed, 605 insertions(+), 6 deletions(-)
diff --git a/doc/src/sgml/xfunc.sgml b/doc/src/sgml/xfunc.sgml
index 30219f432d9..ecd7ced8be6 100644
--- a/doc/src/sgml/xfunc.sgml
+++ b/doc/src/sgml/xfunc.sgml
@@ -4166,6 +4166,107 @@ supportfn(internal) returns internal
expression and an actual execution of the target function.
</para>
+ <para>
+ Similarly, a <link linkend="queries-tablefunctions">set-returning function</link>
+ can implement <literal>SupportRequestInlineSRF</literal> to return a
+ <literal>Query</literal> node, which the planner will try to inline into
+ the outer query, just as <productname>PostgreSQL</productname> inlines
+ SQL functions. Normallly only SQL functions can be inlined, but this support
+ request allows a function in <link linkend="plpgsql">PL/pgSQL</link>
+ or another language to build a dynamic SQL query and have it inlined too.
+ The <literal>Query</literal> node must be a <literal>SELECT</literal> query
+ that has gone through parse analysis and rewriting.
+ You may include <literal>Param</literal> nodes referencing the original function's
+ parameters, and <productname>PostgreSQL</productname> will map those appropriately
+ to the arguments passed by the caller.
+ It is the responsibility of the support function to return
+ a node that matches the parent function's implementation.
+ We make no guarantee that <productname>PostgreSQL</productname> will
+ never call the target function in cases that the support function could
+ simplify. Functions called in <literal>SELECT</literal> are not simplified.
+ Or if the <literal>RangeTblEntry</literal> has more than one
+ <literal>RangeTblFunction</literal> (such as when using
+ <literal>ROWS FROM</literal>), the function will not be simplified.
+ Ensure rigorous equivalence between the simplified expression and an actual
+ execution of the target function.
+ </para>
+
+ <para>
+ One way to implement a <literal>SupportRequestInlineSRF</literal> support function
+ is to build a SQL string then parse it with <literal>pg_parse_query</literal>.
+ The outline of such a function might look like this:
+<programlisting>
+PG_FUNCTION_INFO_V1(my_support_function);
+Datum
+my_support_function(PG_FUNCTION_ARGS)
+{
+ Node *rawreq = (Node *) PG_GETARG_POINTER(0);
+ SupportRequestInlineSRF *req
+ RangeTblFunction *rtfunc;
+ FuncExpr *expr;
+ Query *querytree;
+ StringInfoData sql;
+ HeapTuple func_tuple;
+ SQLFunctionParseInfoPtr pinfo;
+ List *raw_parsetree_list;
+
+ /* Return if it's not a type we handle. */
+ if (!IsA(rawreq, SupportRequestInlineSRF))
+ PG_RETURN_POINTER(NULL);
+
+ /* Get things we need off the support request node. */
+ req = (SupportRequestInlineSRF *) rawreq;
+ rtfunc = req->rtfunc;
+ expr = (FuncExpr *) rtfunc->funcexpr;
+
+ /* Generate the SQL string. */
+ initStringInfo(&sql);
+ appendStringInfo(&sql, "SELECT ...");
+
+ /* Build a SQLFunctionParseInfo. */
+ func_tuple = SearchSysCache1(PROCOID, ObjectIdGetDatum(expr->funcid));
+ if (!HeapTupleIsValid(func_tuple))
+ {
+ ereport(WARNING, (errmsg("cache lookup failed for function %u", expr->funcid)));
+ PG_RETURN_POINTER(NULL);
+ }
+ pinfo = prepare_sql_fn_parse_info(func_tuple,
+ (Node *) expr,
+ expr->inputcollid);
+ ReleaseSysCache(func_tuple);
+
+ /* Parse the SQL. */
+ raw_parsetree_list = pg_parse_query(sql.data);
+ if (list_length(raw_parsetree_list) != 1)
+ {
+ ereport(WARNING, (errmsg("my_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("my_support_func rewrote to more than one node")));
+ PG_RETURN_POINTER(NULL);
+ }
+
+ querytree = linitial(querytree_list);
+ if (!IsA(querytree, Query))
+ {
+ ereport(WARNING, (errmsg("my_support_func didn't parse to a Query"),
+ errdetail("Got this instead: %s", nodeToString(querytree))));
+ PG_RETURN_POINTER(NULL);
+ }
+
+ PG_RETURN_POINTER(querytree);
+}
+</programlisting>
+ </para>
+
<para>
For target functions that return <type>boolean</type>, it is often useful to estimate
the fraction of rows that will be selected by a <literal>WHERE</literal> clause using that
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index 77f48ff4069..312237a5e13 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -5145,6 +5145,47 @@ evaluate_expr(Expr *expr, Oid result_type, int32 result_typmod,
}
+/*
+ * inline_set_returning_function_with_support
+ *
+ * This implements inline_set_returning_function for functions with
+ * a support function that can handle SupportRequestInlineSRF.
+ * We check fewer things than inline_sql_set_returning_function,
+ * so that support functions can make their own decisions about what
+ * to handle. For instance we don't forbid a VOLATILE function.
+ */
+static Query *
+inline_set_returning_function_with_support(PlannerInfo *root, RangeTblEntry *rte,
+ RangeTblFunction *rtfunc,
+ FuncExpr *fexpr, HeapTuple func_tuple,
+ Form_pg_proc funcform)
+{
+ SupportRequestInlineSRF req;
+ Node *newnode;
+
+ /* It must have a support function. */
+ Assert(funcform->prosupport);
+
+ req.root = root;
+ req.type = T_SupportRequestInlineSRF;
+ req.rtfunc = rtfunc;
+ req.proc = func_tuple;
+
+ newnode = (Node *)
+ DatumGetPointer(OidFunctionCall1(funcform->prosupport,
+ PointerGetDatum(&req)));
+
+ if (!newnode)
+ return NULL;
+
+ if (!IsA(newnode, Query))
+ elog(ERROR,
+ "Got unexpected node type %d from %s for function %s",
+ newnode->type, "SupportRequestInlineSRF", NameStr(funcform->proname));
+
+ return (Query *) newnode;
+}
+
/*
* inline_sql_set_returning_function
*
@@ -5310,10 +5351,10 @@ inline_sql_set_returning_function(PlannerInfo *root, RangeTblEntry *rte,
/*
* inline_set_returning_function
- * Attempt to "inline" an SQL set-returning function in the FROM clause.
+ * Attempt to "inline" a set-returning 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
+ * set-returning function that can safely 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
@@ -5341,7 +5382,7 @@ inline_set_returning_function(PlannerInfo *root, RangeTblEntry *rte)
ErrorContextCallback sqlerrcontext;
MemoryContext oldcxt;
MemoryContext mycxt;
- Query *querytree;
+ Query *querytree = NULL;
Assert(rte->rtekind == RTE_FUNCTION);
@@ -5429,9 +5470,19 @@ inline_set_returning_function(PlannerInfo *root, RangeTblEntry *rte)
if (!heap_attisnull(func_tuple, Anum_pg_proc_proconfig, NULL))
goto fail;
- querytree = inline_sql_set_returning_function(root, rte, rtfunc, fexpr,
- func_oid, func_tuple, funcform,
- src);
+ /*
+ * If the function has an attached support function that can handle
+ * SupportRequestInlineSRF, then attempt to inline with that. Return the
+ * result if we get one, otherwise proceed.
+ */
+ if (funcform->prosupport)
+ querytree = inline_set_returning_function_with_support(root, rte, rtfunc, fexpr,
+ func_tuple, funcform);
+
+ /* Try to inline automatically */
+ if (!querytree)
+ querytree = inline_sql_set_returning_function(root, rte, rtfunc, fexpr,
+ func_oid, func_tuple, funcform, src);
if (!querytree)
goto fail;
diff --git a/src/include/nodes/supportnodes.h b/src/include/nodes/supportnodes.h
index 9c047cc401b..94f8a52de2b 100644
--- a/src/include/nodes/supportnodes.h
+++ b/src/include/nodes/supportnodes.h
@@ -33,6 +33,7 @@
#ifndef SUPPORTNODES_H
#define SUPPORTNODES_H
+#include "catalog/pg_proc.h"
#include "nodes/plannodes.h"
struct PlannerInfo; /* avoid including pathnodes.h here */
@@ -69,6 +70,39 @@ typedef struct SupportRequestSimplify
FuncExpr *fcall; /* Function call to be simplified */
} SupportRequestSimplify;
+/*
+ * The InlineSRF request allows the support function to perform plan-time
+ * simplification of a call to its target set-returning function. For
+ * example a PL/pgSQL function could build a dynamic SQL query and execute it.
+ * Normally only SQL functions can be inlined, but with this support function
+ * the dynamic query can be inlined as well.
+ *
+ * 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 function being replaced.
+ * The support function is only called if rtfunc->functions contains a
+ * single FuncExpr node. (ROWS FROM is one way to get more than one.)
+ *
+ * "proc" will be the HeapTuple for the pg_proc record of the function being
+ * replaced.
+ *
+ * The result should be a semantically-equivalent transformed node tree,
+ * or NULL if no simplification could be performed. It should be allocated
+ * in the CurrentMemoryContext. Do *not* return or modify the FuncExpr node
+ * tree, as it isn't really a separately allocated Node. But it's okay to
+ * use its args, or parts of it, in the result tree.
+ */
+typedef struct SupportRequestInlineSRF
+{
+ NodeTag type;
+
+ struct PlannerInfo *root; /* Planner's infrastructure */
+ RangeTblFunction *rtfunc; /* Function call to be simplified */
+ HeapTuple proc; /* Function definition from pg_proc */
+} SupportRequestInlineSRF;
+
/*
* 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/test/regress/expected/misc_functions.out b/src/test/regress/expected/misc_functions.out
index c3b2b9d8603..13388ebc715 100644
--- a/src/test/regress/expected/misc_functions.out
+++ b/src/test/regress/expected/misc_functions.out
@@ -601,6 +601,11 @@ CREATE FUNCTION test_support_func(internal)
RETURNS internal
AS :'regresslib', 'test_support_func'
LANGUAGE C STRICT;
+-- With a support function that inlines SRFs
+CREATE FUNCTION test_inline_srf_support_func(internal)
+ RETURNS internal
+ AS :'regresslib', 'test_inline_srf_support_func'
+ LANGUAGE C STRICT;
ALTER FUNCTION my_int_eq(int, int) SUPPORT test_support_func;
EXPLAIN (COSTS OFF)
SELECT * FROM tenk1 a JOIN tenk1 b ON a.unique1 = b.unique1
@@ -777,6 +782,213 @@ false, true, false, true);
Function Scan on generate_series g (cost=N..N rows=1000 width=N)
(1 row)
+--
+-- Test inlining PL/pgSQL functions
+--
+-- RETURNS SETOF TEXT:
+CREATE OR REPLACE 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 LEAKPROOF;
+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 SELECT * FROM foo_from_bar('f1', 'text_tbl', NULL);
+ QUERY PLAN
+----------------------------------------------------------------------
+ Function Scan on foo_from_bar (cost=0.25..10.25 rows=1000 width=32)
+(1 row)
+
+EXPLAIN SELECT * FROM foo_from_bar('f1', 'text_tbl', 'doh!');
+ QUERY PLAN
+----------------------------------------------------------------------
+ Function Scan on foo_from_bar (cost=0.25..10.25 rows=1000 width=32)
+(1 row)
+
+ALTER FUNCTION foo_from_bar(TEXT, TEXT, TEXT) SUPPORT test_inline_srf_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 SELECT * FROM foo_from_bar('f1', 'text_tbl', NULL);
+ QUERY PLAN
+---------------------------------------------------------
+ Seq Scan on text_tbl (cost=0.00..1.02 rows=2 width=32)
+(1 row)
+
+EXPLAIN SELECT * FROM foo_from_bar('f1', 'text_tbl', 'doh!');
+ QUERY PLAN
+---------------------------------------------------------
+ Seq Scan on text_tbl (cost=0.00..1.02 rows=1 width=32)
+ Filter: (f1 = 'doh!'::text)
+(2 rows)
+
+DROP FUNCTION foo_from_bar;
+-- RETURNS SETOF RECORD:
+CREATE OR REPLACE FUNCTION foo_from_bar(colname TEXT, tablename TEXT, filter TEXT)
+RETURNS SETOF RECORD
+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 LEAKPROOF;
+SELECT * FROM foo_from_bar('f1', 'text_tbl', NULL) AS bar(foo TEXT);
+ foo
+-------------------
+ doh!
+ hi de ho neighbor
+(2 rows)
+
+SELECT * FROM foo_from_bar('f1', 'text_tbl', 'doh!') AS bar(foo TEXT);
+ foo
+------
+ doh!
+(1 row)
+
+EXPLAIN SELECT * FROM foo_from_bar('f1', 'text_tbl', NULL) AS bar(foo TEXT);
+ QUERY PLAN
+--------------------------------------------------------------------------
+ Function Scan on foo_from_bar bar (cost=0.25..10.25 rows=1000 width=32)
+(1 row)
+
+EXPLAIN SELECT * FROM foo_from_bar('f1', 'text_tbl', 'doh!') AS bar(foo TEXT);
+ QUERY PLAN
+--------------------------------------------------------------------------
+ Function Scan on foo_from_bar bar (cost=0.25..10.25 rows=1000 width=32)
+(1 row)
+
+ALTER FUNCTION foo_from_bar(TEXT, TEXT, TEXT) SUPPORT test_inline_srf_support_func;
+SELECT * FROM foo_from_bar('f1', 'text_tbl', NULL) AS bar(foo TEXT);
+ foo
+-------------------
+ doh!
+ hi de ho neighbor
+(2 rows)
+
+SELECT * FROM foo_from_bar('f1', 'text_tbl', 'doh!') AS bar(foo TEXT);
+ foo
+------
+ doh!
+(1 row)
+
+EXPLAIN SELECT * FROM foo_from_bar('f1', 'text_tbl', NULL) AS bar(foo TEXT);
+ QUERY PLAN
+---------------------------------------------------------
+ Seq Scan on text_tbl (cost=0.00..1.02 rows=2 width=32)
+(1 row)
+
+EXPLAIN SELECT * FROM foo_from_bar('f1', 'text_tbl', 'doh!') AS bar(foo TEXT);
+ QUERY PLAN
+---------------------------------------------------------
+ Seq Scan on text_tbl (cost=0.00..1.02 rows=1 width=32)
+ Filter: (f1 = 'doh!'::text)
+(2 rows)
+
+DROP FUNCTION foo_from_bar;
+-- RETURNS TABLE:
+CREATE OR REPLACE FUNCTION foo_from_bar(colname TEXT, tablename TEXT, filter TEXT)
+RETURNS TABLE(foo 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 LEAKPROOF;
+SELECT * FROM foo_from_bar('f1', 'text_tbl', NULL);
+ foo
+-------------------
+ doh!
+ hi de ho neighbor
+(2 rows)
+
+SELECT * FROM foo_from_bar('f1', 'text_tbl', 'doh!');
+ foo
+------
+ doh!
+(1 row)
+
+EXPLAIN SELECT * FROM foo_from_bar('f1', 'text_tbl', NULL);
+ QUERY PLAN
+----------------------------------------------------------------------
+ Function Scan on foo_from_bar (cost=0.25..10.25 rows=1000 width=32)
+(1 row)
+
+EXPLAIN SELECT * FROM foo_from_bar('f1', 'text_tbl', 'doh!');
+ QUERY PLAN
+----------------------------------------------------------------------
+ Function Scan on foo_from_bar (cost=0.25..10.25 rows=1000 width=32)
+(1 row)
+
+ALTER FUNCTION foo_from_bar(TEXT, TEXT, TEXT) SUPPORT test_inline_srf_support_func;
+SELECT * FROM foo_from_bar('f1', 'text_tbl', NULL);
+ foo
+-------------------
+ doh!
+ hi de ho neighbor
+(2 rows)
+
+SELECT * FROM foo_from_bar('f1', 'text_tbl', 'doh!');
+ foo
+------
+ doh!
+(1 row)
+
+EXPLAIN SELECT * FROM foo_from_bar('f1', 'text_tbl', NULL);
+ QUERY PLAN
+---------------------------------------------------------
+ Seq Scan on text_tbl (cost=0.00..1.02 rows=2 width=32)
+(1 row)
+
+EXPLAIN SELECT * FROM foo_from_bar('f1', 'text_tbl', 'doh!');
+ QUERY PLAN
+---------------------------------------------------------
+ Seq Scan on text_tbl (cost=0.00..1.02 rows=1 width=32)
+ 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 3dbba069024..eadb40369d4 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,11 +40,13 @@
#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"
#include "utils/memutils.h"
#include "utils/rel.h"
+#include "utils/syscache.h"
#include "utils/typcache.h"
#define EXPECT_TRUE(expr) \
@@ -803,6 +806,117 @@ test_support_func(PG_FUNCTION_ARGS)
PG_RETURN_POINTER(ret);
}
+PG_FUNCTION_INFO_V1(test_inline_srf_support_func);
+Datum
+test_inline_srf_support_func(PG_FUNCTION_ARGS)
+{
+ Node *rawreq = (Node *) PG_GETARG_POINTER(0);
+ Query *querytree = NULL;
+
+ if (IsA(rawreq, SupportRequestInlineSRF))
+ {
+ /*
+ * Assume that the target is foo_from_bar; that's safe as long as we
+ * don't attach this to any other set-returning function.
+ */
+ SupportRequestInlineSRF *req = (SupportRequestInlineSRF *) 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;
+
+ if (list_length(expr->args) != 3)
+ {
+ ereport(WARNING, (errmsg("test_inline_srf_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_srf_support_func called with non-Const parameters")));
+ PG_RETURN_POINTER(NULL);
+ }
+
+ c = (Const *) node;
+ if (c->consttype != TEXTOID)
+ {
+ ereport(WARNING, (errmsg("test_inline_srf_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_srf_support_func called with non-Const parameters")));
+ PG_RETURN_POINTER(NULL);
+ }
+
+ c = (Const *) node;
+ if (c->consttype != TEXTOID)
+ {
+ ereport(WARNING, (errmsg("test_inline_srf_support_func called with non-TEXT parameters")));
+ PG_RETURN_POINTER(NULL);
+ }
+ tablename = TextDatumGetCString(c->constvalue);
+
+ initStringInfo(&sql);
+ appendStringInfo(&sql, "SELECT %s::text FROM %s", quote_identifier(colname), quote_identifier(tablename));
+
+ /* Get filter if present */
+ node = lthird(expr->args);
+ if (!(IsA(node, Const) && ((Const *) node)->constisnull))
+ {
+ /* Only filter if $3 is Const */
+ appendStringInfo(&sql, " WHERE %s::text = $3", quote_identifier(colname));
+ }
+
+ /* Build a SQLFunctionParseInfo. */
+
+ pinfo = prepare_sql_fn_parse_info(req->proc,
+ (Node *) expr,
+ expr->inputcollid);
+
+ /* Parse the SQL. */
+ raw_parsetree_list = pg_parse_query(sql.data);
+ if (list_length(raw_parsetree_list) != 1)
+ {
+ ereport(WARNING, (errmsg("test_inline_srf_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_srf_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_srf_support_func didn't parse to a Query"),
+ errdetail("Got this instead: %s", nodeToString(querytree))));
+ PG_RETURN_POINTER(NULL);
+ }
+ }
+
+ PG_RETURN_POINTER(querytree);
+}
+
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 23792c4132a..56c6e7a8a88 100644
--- a/src/test/regress/sql/misc_functions.sql
+++ b/src/test/regress/sql/misc_functions.sql
@@ -248,6 +248,12 @@ CREATE FUNCTION test_support_func(internal)
AS :'regresslib', 'test_support_func'
LANGUAGE C STRICT;
+-- With a support function that inlines SRFs
+CREATE FUNCTION test_inline_srf_support_func(internal)
+ RETURNS internal
+ AS :'regresslib', 'test_inline_srf_support_func'
+ LANGUAGE C STRICT;
+
ALTER FUNCTION my_int_eq(int, int) SUPPORT test_support_func;
EXPLAIN (COSTS OFF)
@@ -349,6 +355,86 @@ SELECT explain_mask_costs($$
SELECT * FROM generate_series(25.0, 2.0, 0.0) g(s);$$,
false, true, false, true);
+--
+-- Test inlining PL/pgSQL functions
+--
+
+-- RETURNS SETOF TEXT:
+CREATE OR REPLACE 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 LEAKPROOF;
+SELECT * FROM foo_from_bar('f1', 'text_tbl', NULL);
+SELECT * FROM foo_from_bar('f1', 'text_tbl', 'doh!');
+EXPLAIN SELECT * FROM foo_from_bar('f1', 'text_tbl', NULL);
+EXPLAIN SELECT * FROM foo_from_bar('f1', 'text_tbl', 'doh!');
+ALTER FUNCTION foo_from_bar(TEXT, TEXT, TEXT) SUPPORT test_inline_srf_support_func;
+SELECT * FROM foo_from_bar('f1', 'text_tbl', NULL);
+SELECT * FROM foo_from_bar('f1', 'text_tbl', 'doh!');
+EXPLAIN SELECT * FROM foo_from_bar('f1', 'text_tbl', NULL);
+EXPLAIN SELECT * FROM foo_from_bar('f1', 'text_tbl', 'doh!');
+DROP FUNCTION foo_from_bar;
+-- RETURNS SETOF RECORD:
+CREATE OR REPLACE FUNCTION foo_from_bar(colname TEXT, tablename TEXT, filter TEXT)
+RETURNS SETOF RECORD
+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 LEAKPROOF;
+SELECT * FROM foo_from_bar('f1', 'text_tbl', NULL) AS bar(foo TEXT);
+SELECT * FROM foo_from_bar('f1', 'text_tbl', 'doh!') AS bar(foo TEXT);
+EXPLAIN SELECT * FROM foo_from_bar('f1', 'text_tbl', NULL) AS bar(foo TEXT);
+EXPLAIN SELECT * FROM foo_from_bar('f1', 'text_tbl', 'doh!') AS bar(foo TEXT);
+ALTER FUNCTION foo_from_bar(TEXT, TEXT, TEXT) SUPPORT test_inline_srf_support_func;
+SELECT * FROM foo_from_bar('f1', 'text_tbl', NULL) AS bar(foo TEXT);
+SELECT * FROM foo_from_bar('f1', 'text_tbl', 'doh!') AS bar(foo TEXT);
+EXPLAIN SELECT * FROM foo_from_bar('f1', 'text_tbl', NULL) AS bar(foo TEXT);
+EXPLAIN SELECT * FROM foo_from_bar('f1', 'text_tbl', 'doh!') AS bar(foo TEXT);
+DROP FUNCTION foo_from_bar;
+-- RETURNS TABLE:
+CREATE OR REPLACE FUNCTION foo_from_bar(colname TEXT, tablename TEXT, filter TEXT)
+RETURNS TABLE(foo 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 LEAKPROOF;
+SELECT * FROM foo_from_bar('f1', 'text_tbl', NULL);
+SELECT * FROM foo_from_bar('f1', 'text_tbl', 'doh!');
+EXPLAIN SELECT * FROM foo_from_bar('f1', 'text_tbl', NULL);
+EXPLAIN SELECT * FROM foo_from_bar('f1', 'text_tbl', 'doh!');
+ALTER FUNCTION foo_from_bar(TEXT, TEXT, TEXT) SUPPORT test_inline_srf_support_func;
+SELECT * FROM foo_from_bar('f1', 'text_tbl', NULL);
+SELECT * FROM foo_from_bar('f1', 'text_tbl', 'doh!');
+EXPLAIN SELECT * FROM foo_from_bar('f1', 'text_tbl', NULL);
+EXPLAIN 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 e6f2e93b2d6..8b681e9e44e 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2900,6 +2900,7 @@ SubscriptionRelState
SummarizerReadLocalXLogPrivate
SupportRequestCost
SupportRequestIndexCondition
+SupportRequestInlineSRF
SupportRequestModifyInPlace
SupportRequestOptimizeWindowClause
SupportRequestRows
--
2.45.0
view thread (19+ messages) latest in thread
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 using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Inline non-SQL SRFs using SupportRequestSimplify
In-Reply-To: <CA+renyXxanvX8rnrqTNkZt1r9=rm59MHSG4DDVfsV2aUGe3dUQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox