On 7/26/24 11:58, Tom Lane wrote:
> Heikki Linnakangas <hlinn...@iki.fi> writes:
>> On 28/06/2024 01:01, Paul Jungwirth wrote:
>>> Another approach I considered is using a separate support request, e.g.
SupportRequestInlineSRF, and
>>> just calling it from inline_set_returning_function. I didn't like having
two support requests that
>>> did almost exactly the same thing. OTOH my current approach means you'll get an error if you do
this:
>>>
>>> ```
>>> postgres=# select temporal_semijoin('employees', 'id', 'valid_at',
'positions', 'employee_id',
>>> 'valid_at');
>>> ERROR: unrecognized node type: 66
>>> ```
>>>
>>> I'll look into ways to fix that.
>
> I like this idea, but I like exactly nothing about this implementation.
> The right thing is to have a separate SupportRequestInlineSRF request
> that is called directly by inline_set_returning_function.
Here are new patches using a new SupportRequestInlineSRF request type. They include patches and
documentation.
The patches handle this:
SELECT * FROM srf();
but not this:
SELECT srf();
In the latter case, Postgres always calls the function in "materialized mode" and gets the whole
result up front, so inline_set_returning_function is never called, even for SQL functions.
For tests I added a `foo_from_bar(colname, tablename, filter)` PL/pgSQL function that does `SELECT
$colname FROM $tablename [WHERE $colname = $filter]`, then the support function generates the same
SQL and turns it into a Query node. This matches how I want to use the feature for my
temporal_semijoin etc functions. If you give a non-NULL filter, you get a Query with a Var node, so
we are testing something that isn't purely Const.
The SupportRequestSimplify type has some comments about supporting operators, but I don't think you
can have a set-returning operator, so I didn't repeat those comments for this new type.
I split things up into three patch files because I couldn't get git to gracefully handle shifting a
large block of code into an if statement. The first two patches have no changes except that
indentation (and initializing one variable to NULL). They aren't meant to be committed separately.
Rebased to a83a944e9f.
Yours,
--
Paul ~{:-)
p...@illuminatedcomputing.comFrom 591de3a06ec97cf1bff10d603946f0bac176b9d9 Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <p...@illuminatedcomputing.com>
Date: Thu, 29 Aug 2024 18:17:55 -0700
Subject: [PATCH v2 1/3] Add indented section
---
src/backend/optimizer/util/clauses.c | 74 +++++++++++++++++++++++++++-
1 file changed, 73 insertions(+), 1 deletion(-)
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index b4e085e9d4b..ef8282ded49 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -5069,7 +5069,7 @@ inline_set_returning_function(PlannerInfo *root, RangeTblEntry *rte)
TupleDesc rettupdesc;
List *raw_parsetree_list;
List *querytree_list;
- Query *querytree;
+ Query *querytree = NULL;
Assert(rte->rtekind == RTE_FUNCTION);
@@ -5235,6 +5235,78 @@ inline_set_returning_function(PlannerInfo *root, RangeTblEntry *rte)
goto fail;
querytree = linitial(querytree_list);
}
+ if (!querytree)
+ {
+ /* 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 = (void *) &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);
+ if (!isNull)
+ {
+ Node *n;
+
+ n = stringToNode(TextDatumGetCString(tmp));
+ 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;
+ 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;
+ querytree = linitial(querytree_list);
+ }
+ else
+ {
+ /*
+ * Set up to handle parameters while parsing the function body. We
+ * can use the FuncExpr just created as the input for
+ * prepare_sql_fn_parse_info.
+ */
+ pinfo = prepare_sql_fn_parse_info(func_tuple,
+ (Node *) fexpr,
+ fexpr->inputcollid);
+
+ /*
+ * Parse, analyze, and rewrite (unlike inline_function(), we can't
+ * skip rewriting here). We can fail as soon as we find more than one
+ * query, though.
+ */
+ raw_parsetree_list = pg_parse_query(src);
+ if (list_length(raw_parsetree_list) != 1)
+ goto fail;
+
+ 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;
+ querytree = linitial(querytree_list);
+ }
+ }
/*
* Also resolve the actual function result tupdesc, if composite. If we
--
2.42.0
From 7a9ee15d17bee6a6743bf18e7428e6594e17be35 Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <p...@illuminatedcomputing.com>
Date: Thu, 29 Aug 2024 18:18:20 -0700
Subject: [PATCH v2 2/3] Remove unindented section
---
src/backend/optimizer/util/clauses.c | 69 ----------------------------
1 file changed, 69 deletions(-)
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index ef8282ded49..d2d8f7eb0c0 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -5166,75 +5166,6 @@ inline_set_returning_function(PlannerInfo *root, RangeTblEntry *rte)
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 = (void *) &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);
- if (!isNull)
- {
- Node *n;
-
- n = stringToNode(TextDatumGetCString(tmp));
- 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;
- 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;
- querytree = linitial(querytree_list);
- }
- else
- {
- /*
- * Set up to handle parameters while parsing the function body. We
- * can use the FuncExpr just created as the input for
- * prepare_sql_fn_parse_info.
- */
- pinfo = prepare_sql_fn_parse_info(func_tuple,
- (Node *) fexpr,
- fexpr->inputcollid);
-
- /*
- * Parse, analyze, and rewrite (unlike inline_function(), we can't
- * skip rewriting here). We can fail as soon as we find more than one
- * query, though.
- */
- raw_parsetree_list = pg_parse_query(src);
- if (list_length(raw_parsetree_list) != 1)
- goto fail;
-
- 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;
- querytree = linitial(querytree_list);
- }
if (!querytree)
{
/* Fetch the function body */
--
2.42.0
From 4ed50cad16901c5b7b3447bad7105de11a81773a Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <p...@illuminatedcomputing.com>
Date: Thu, 29 Aug 2024 18:20:47 -0700
Subject: [PATCH v2 3/3] Inline non-SQL SRFs using SupportRequestInlineSRF
By inlining functions written in PL/pgSQL (or other languages), we let
users compose dynamic queries without paying the overhead of function
invocations. Formerly this was only possible for SQL functions.
If the support request returns a Query node, then
inline_set_returning_function will use it in place of the FuncExpr.
The Query node can reference the original function's arguments as Var
nodes.
We only try to inline SRFs (even SQL SRFs) when they are used as RTEs,
so inlining happens for `SELECT * FROM foo()` but not for `SELECT
foo()`.
---
doc/src/sgml/xfunc.sgml | 14 ++
src/backend/optimizer/util/clauses.c | 63 +++++-
src/include/nodes/supportnodes.h | 22 ++
src/test/regress/expected/misc_functions.out | 212 +++++++++++++++++++
src/test/regress/regress.c | 91 ++++++++
src/test/regress/sql/misc_functions.sql | 87 ++++++++
6 files changed, 487 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/xfunc.sgml b/doc/src/sgml/xfunc.sgml
index 9bc23a9a938..311a87b14a5 100644
--- a/doc/src/sgml/xfunc.sgml
+++ b/doc/src/sgml/xfunc.sgml
@@ -4076,6 +4076,20 @@ supportfn(internal) returns internal
expression and an actual execution of the target function.
</para>
+ <para>
+ Similarly, a <link linkend="queries-tablefunction">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.
+ It is the responsibility of the support function to return
+ a node that matches the parent function's implementation. One way to do this is
+ to build a SQL string then parse it with <literal>pg_parse_query</literal>.
+ See <literal>regress.c</literal> for an example.
+ </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 d2d8f7eb0c0..9ee5831ed0e 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -5134,7 +5134,8 @@ 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
+ * Forget it unless the function is SQL-language or provides a support function.
+ * Also check for 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
@@ -5143,7 +5144,7 @@ inline_set_returning_function(PlannerInfo *root, RangeTblEntry *rte)
* 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 ||
+ if ((funcform->prolang != SQLlanguageId && !funcform->prosupport) ||
funcform->prokind != PROKIND_FUNCTION ||
funcform->proisstrict ||
funcform->provolatile == PROVOLATILE_VOLATILE ||
@@ -5166,6 +5167,64 @@ inline_set_returning_function(PlannerInfo *root, RangeTblEntry *rte)
ALLOCSET_DEFAULT_SIZES);
oldcxt = MemoryContextSwitchTo(mycxt);
+ /*
+ * If the function has a SupportRequestInlineSRF support function,
+ * see if it can produce a Query node we can inline.
+ * This can be mutually-exclusive with SQL functions.
+ * Those are inlineable already, so it doesn't make sense to
+ * attach such a support request to them.
+ */
+ if (funcform->prolang != SQLlanguageId && funcform->prosupport) {
+ SupportRequestInlineSRF req;
+ FuncExpr dummy_fexpr;
+ Node *newnode;
+
+ /*
+ * We don't need the whole sqlerrcontext,
+ * but make sure we restore it correctly
+ * if we goto fail.
+ */
+ sqlerrcontext.previous = error_context_stack;
+
+ /*
+ * Build a SupportRequestInlineSRF node to pass to the support
+ * function, pointing to a dummy FuncExpr node containing the
+ * simplified arg list. We use this approach to present a uniform
+ * interface to the support function regardless of how the target
+ * function is actually being invoked.
+ */
+
+ dummy_fexpr.xpr.type = T_FuncExpr;
+ dummy_fexpr.funcid = func_oid;
+ dummy_fexpr.funcresulttype = funcform->prorettype;
+ dummy_fexpr.funcretset = funcform->proretset;
+ dummy_fexpr.funcvariadic = OidIsValid(funcform->provariadic);
+ dummy_fexpr.funcformat = COERCE_EXPLICIT_CALL;
+ dummy_fexpr.funccollid = InvalidOid;
+ dummy_fexpr.inputcollid = InvalidOid;
+ dummy_fexpr.args = fexpr->args;
+ dummy_fexpr.location = -1;
+
+ req.type = T_SupportRequestInlineSRF;
+ req.fcall = &dummy_fexpr;
+
+ newnode = (Node *)
+ DatumGetPointer(OidFunctionCall1(funcform->prosupport,
+ PointerGetDatum(&req)));
+
+ if (!newnode)
+ goto fail;
+
+ if (!IsA(newnode, Query))
+ elog(ERROR,
+ "Got unexpected node type %d from SupportRequestInlineSRF for function %s",
+ newnode->type, NameStr(funcform->proname));
+
+ querytree = (Query *) newnode;
+ querytree_list = list_make1(querytree);
+ }
+
+
if (!querytree)
{
/* Fetch the function body */
diff --git a/src/include/nodes/supportnodes.h b/src/include/nodes/supportnodes.h
index 5f7bcde891c..bc0b5cccaf3 100644
--- a/src/include/nodes/supportnodes.h
+++ b/src/include/nodes/supportnodes.h
@@ -69,6 +69,28 @@ 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 plpgsql 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.
+ *
+ * "fcall" will be a FuncExpr invoking the support function's target
+ * function.
+ *
+ * The result should be a semantically-equivalent transformed node tree,
+ * or NULL if no simplification could be performed. Do *not* return or
+ * modify *fcall, as it isn't really a separately allocated Node. But
+ * it's okay to use fcall->args, or parts of it, in the result tree.
+ */
+typedef struct SupportRequestInlineSRF
+{
+ NodeTag type;
+
+ FuncExpr *fcall; /* Function call to be simplified */
+} 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 35fb72f302b..c5bac6d43ea 100644
--- a/src/test/regress/expected/misc_functions.out
+++ b/src/test/regress/expected/misc_functions.out
@@ -595,6 +595,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
@@ -706,6 +711,213 @@ false, true, false, true);
-- the support function.
SELECT * FROM generate_series(TIMESTAMPTZ '2024-02-01', TIMESTAMPTZ '2024-03-01', INTERVAL '0 day') g(s);
ERROR: step size cannot equal zero
+--
+-- 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 14aad5a0c6e..9f97ad6ad45 100644
--- a/src/test/regress/regress.c
+++ b/src/test/regress/regress.c
@@ -29,6 +29,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 "parser/parse_coerce.h"
#include "port/atomics.h"
#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) \
@@ -989,6 +992,94 @@ 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);
+
+ 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;
+ FuncExpr *expr = req->fcall;
+ Node *node;
+ Const *c;
+ char *colname;
+ char *tablename;
+ HeapTuple func_tuple;
+ SQLFunctionParseInfoPtr pinfo;
+ List *raw_parsetree_list;
+ List *querytree_list;
+ Query *querytree;
+
+ if (list_length(expr->args) != 3)
+ ereport(ERROR, (errmsg("test_inline_srf_support_func called with %d args but expected 3", list_length(expr->args))));
+
+ /* Get colname */
+ node = linitial(expr->args);
+ if (!IsA(node, Const))
+ ereport(ERROR, (errmsg("test_inline_srf_support_func called with non-Const parameters")));
+ c = (Const *) node;
+ if (c->consttype != TEXTOID)
+ ereport(ERROR, (errmsg("test_inline_srf_support_func called with non-TEXT parameters")));
+ colname = TextDatumGetCString(c->constvalue);
+
+ /* Get tablename */
+ node = lsecond(expr->args);
+ if (!IsA(node, Const))
+ ereport(ERROR, (errmsg("test_inline_srf_support_func called with non-Const parameters")));
+ c = (Const *) node;
+ if (c->consttype != TEXTOID)
+ ereport(ERROR, (errmsg("test_inline_srf_support_func called with non-TEXT parameters")));
+ 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));
+ }
+
+ func_tuple = SearchSysCache1(PROCOID, ObjectIdGetDatum(expr->funcid));
+ if (!HeapTupleIsValid(func_tuple))
+ elog(ERROR, "cache lookup failed for function %u", expr->funcid);
+
+ pinfo = prepare_sql_fn_parse_info(func_tuple,
+ (Node *) expr,
+ expr->inputcollid);
+
+ ReleaseSysCache(func_tuple);
+
+ raw_parsetree_list = pg_parse_query(sql.data);
+ if (list_length(raw_parsetree_list) != 1)
+ elog(ERROR, "test_inline_srf_support_func parsed to more than one node");
+
+ 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)
+ elog(ERROR, "test_inline_srf_support_func rewrote to more than one node");
+
+ querytree = linitial(querytree_list);
+ if (!IsA(querytree, Query))
+ ereport(ERROR, (errmsg("test_inline_srf_support_func didn't parse to a Query"),
+ errdetail("Got this instead: %s", nodeToString(querytree))));
+ 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 e570783453c..bf692edd0b7 100644
--- a/src/test/regress/sql/misc_functions.sql
+++ b/src/test/regress/sql/misc_functions.sql
@@ -247,6 +247,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)
@@ -265,6 +271,8 @@ SELECT * FROM tenk1 a JOIN my_gen_series(1,1000) g ON a.unique1 = g;
EXPLAIN (COSTS OFF)
SELECT * FROM tenk1 a JOIN my_gen_series(1,10) g ON a.unique1 = g;
+
+
--
-- Test the SupportRequestRows support function for generate_series_timestamp()
--
@@ -310,6 +318,85 @@ false, true, false, true);
-- the support function.
SELECT * FROM generate_series(TIMESTAMPTZ '2024-02-01', TIMESTAMPTZ '2024-03-01', INTERVAL '0 day') g(s);
+--
+-- 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.42.0
