On 9/3/24 09:42, Tom Lane wrote:
Paul Jungwirth <p...@illuminatedcomputing.com> writes:
Here are new patches using a new SupportRequestInlineSRF request type. They
include patches and
documentation.
I took a look through this. I feel like we're still some way away
from having something committable. I've got two main complaint
areas:
1. It doesn't seem like integrating this into
inline_set_returning_function was the right thing after all, or
maybe just the way you did it isn't right.
> ...
2. The documentation needs to be a great deal more explicit
about what the function is supposed to return.
Thanks for the review . . . and your patience waiting for an update!
I tried a few refactoring approaches but the nicest seemed to be to keep the shared parts in
inline_set_returning_function, but have it call out to either inline_sql_set_returning_function or
inline_set_returning_function_with_support. The first patch just refactors but doesn't yet add
inline_set_returning_function_with_support, then the second patch adds the new functionality.
The refactor lets us share lots of pre-condition checks, as well as parameter substitution into the
Query result. In some cases the refactor changes the order of things, but all of those changes
looked safe to me. I didn't love passing a SysCache HeapTuple into another function, but it does
make the cleanup a little easier, since now we can always release it in the same place.
The first patch gave me a wacky diff, but I couldn't get git to make something less fragmented. The
idea is simple though: move part of inline_set_returning_function into
inline_sql_set_returning_function, and call that instead.
Rebased to 0836683a89.
Yours,
--
Paul ~{:-)
p...@illuminatedcomputing.com
From 2a0d2bb6cc6fe25cab1065411ed4db17cc394c53 Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <p...@illuminatedcomputing.com>
Date: Tue, 24 Jun 2025 19:10:15 -0700
Subject: [PATCH v2 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 <p...@illuminatedcomputing.com>
---
src/backend/optimizer/util/clauses.c | 212 +++++++++++++++------------
1 file changed, 122 insertions(+), 90 deletions(-)
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index 26a3e050086..a0dc5dfcd61 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -5049,29 +5049,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).
+ * It allocates its own temporary MemoryContext for the parsing, then copies
+ * the result into the caller's context.
*
- * 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)
{
- RangeTblFunction *rtfunc;
- FuncExpr *fexpr;
- Oid func_oid;
- HeapTuple func_tuple;
- Form_pg_proc funcform;
char *src;
Datum tmp;
bool isNull;
@@ -5088,37 +5080,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
- * 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
@@ -5132,22 +5093,6 @@ inline_set_returning_function(PlannerInfo *root, RangeTblEntry *rte)
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
@@ -5168,7 +5113,6 @@ inline_set_returning_function(PlannerInfo *root, RangeTblEntry *rte)
list_length(fexpr->args) != funcform->pronargs ||
!heap_attisnull(func_tuple, Anum_pg_proc_proconfig, NULL))
{
- ReleaseSysCache(func_tuple);
return NULL;
}
@@ -5177,7 +5121,7 @@ inline_set_returning_function(PlannerInfo *root, RangeTblEntry *rte)
* that parsing might create.
*/
mycxt = AllocSetContextCreate(CurrentMemoryContext,
- "inline_set_returning_function",
+ "inline_sql_set_returning_function",
ALLOCSET_DEFAULT_SIZES);
oldcxt = MemoryContextSwitchTo(mycxt);
@@ -5267,13 +5211,6 @@ inline_set_returning_function(PlannerInfo *root, RangeTblEntry *rte)
else
functypclass = get_expr_result_type((Node *) fexpr, NULL, &rettupdesc);
- /*
- * The single command must be a plain SELECT.
- */
- if (!IsA(querytree, Query) ||
- querytree->commandType != CMD_SELECT)
- goto fail;
-
/*
* Make sure the function (still) returns what it's declared to. This
* will raise an error if wrong, but that's okay since the function would
@@ -5302,13 +5239,6 @@ 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.
@@ -5319,7 +5249,112 @@ inline_set_returning_function(PlannerInfo *root, RangeTblEntry *rte)
MemoryContextDelete(mycxt);
error_context_stack = sqlerrcontext.previous;
- ReleaseSysCache(func_tuple);
+
+ return querytree;
+
+ /* Here if func is not inlinable: release temp memory and return NULL */
+fail:
+ MemoryContextSwitchTo(oldcxt);
+ MemoryContextDelete(mycxt);
+ error_context_stack = sqlerrcontext.previous;
+
+ return NULL;
+}
+
+/*
+ * 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.
+ */
+Query *
+inline_set_returning_function(PlannerInfo *root, RangeTblEntry *rte)
+{
+ RangeTblFunction *rtfunc;
+ FuncExpr *fexpr;
+ Oid func_oid;
+ HeapTuple func_tuple;
+ Form_pg_proc funcform;
+ Query *funcquery;
+
+ 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;
+
+ /*
+ * 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;
+
+ /* 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);
+
+ funcquery = inline_sql_set_returning_function(root, rte, rtfunc, fexpr,
+ func_oid, func_tuple, funcform);
+
+ if (!funcquery)
+ goto fail;
+
+ /*
+ * The single command must be a plain SELECT.
+ */
+ if (!IsA(funcquery, Query) ||
+ funcquery->commandType != CMD_SELECT)
+ goto fail;
+
+ /*
+ * Looks good --- substitute parameters into the query.
+ */
+ funcquery = substitute_actual_srf_parameters(funcquery,
+ funcform->pronargs,
+ fexpr->args);
/*
* We don't have to fix collations here because the upper query is already
@@ -5336,18 +5371,15 @@ inline_set_returning_function(PlannerInfo *root, RangeTblEntry *rte)
* We must also notice if the inserted query adds a dependency on the
* calling role due to RLS quals.
*/
- if (querytree->hasRowSecurity)
+ if (funcquery->hasRowSecurity)
root->glob->dependsOnRole = true;
- return querytree;
+ ReleaseSysCache(func_tuple);
+
+ return funcquery;
- /* 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;
}
--
2.39.5
From c48655640c56055ee5d10fd6feb804a88d7eee29 Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <p...@illuminatedcomputing.com>
Date: Tue, 24 Jun 2025 21:30:02 -0700
Subject: [PATCH v2 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 <p...@illuminatedcomputing.com>
---
doc/src/sgml/xfunc.sgml | 101 +++++++++
src/backend/optimizer/util/clauses.c | 65 +++++-
src/include/nodes/supportnodes.h | 33 +++
src/test/regress/expected/misc_functions.out | 212 +++++++++++++++++++
src/test/regress/regress.c | 122 +++++++++++
src/test/regress/sql/misc_functions.sql | 86 ++++++++
6 files changed, 614 insertions(+), 5 deletions(-)
diff --git a/doc/src/sgml/xfunc.sgml b/doc/src/sgml/xfunc.sgml
index 2d81afce8cb..ef41ce49e65 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 a0dc5dfcd61..c9304c2b8fb 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -5048,6 +5048,50 @@ 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 let the support function make its own decisions about what it can
+ * handle. For instance we don't forbid a VOLATILE function or volatile
+ * arguments.
+ */
+static Query *
+inline_set_returning_function_with_support(PlannerInfo *root, RangeTblEntry *rte,
+ RangeTblFunction *rtfunc,
+ FuncExpr *fexpr, 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 = funcform;
+
+ /*
+ * XXX: wrap this in its own memory context, as
+ * inline_sql_set_returning_function does below?
+ */
+ 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
*
@@ -5263,10 +5307,10 @@ fail:
/*
* 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
@@ -5285,7 +5329,7 @@ inline_set_returning_function(PlannerInfo *root, RangeTblEntry *rte)
Oid func_oid;
HeapTuple func_tuple;
Form_pg_proc funcform;
- Query *funcquery;
+ Query *funcquery = NULL;
Assert(rte->rtekind == RTE_FUNCTION);
@@ -5336,8 +5380,19 @@ inline_set_returning_function(PlannerInfo *root, RangeTblEntry *rte)
elog(ERROR, "cache lookup failed for function %u", func_oid);
funcform = (Form_pg_proc) GETSTRUCT(func_tuple);
- funcquery = inline_sql_set_returning_function(root, rte, rtfunc, fexpr,
- func_oid, func_tuple, funcform);
+ /*
+ * 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)
+ funcquery = inline_set_returning_function_with_support(root, rte, rtfunc, fexpr,
+ funcform);
+
+ /* Try to inline automatically */
+ if (!funcquery)
+ funcquery = inline_sql_set_returning_function(root, rte, rtfunc, fexpr,
+ func_oid, func_tuple, funcform);
if (!funcquery)
goto fail;
diff --git a/src/include/nodes/supportnodes.h b/src/include/nodes/supportnodes.h
index 9c047cc401b..2016e8def65 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,38 @@ 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 Form_pg_proc record for 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 */
+ Form_pg_proc proc; /* Function definition */
+} 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..626ec3a1a71 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,125 @@ 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;
+ HeapTuple func_tuple;
+ 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))
+ {
+ appendStringInfo(&sql, " WHERE %s::text = $3", quote_identifier(colname));
+ }
+
+ /* 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("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();
--
2.39.5
