Hello Hackers,
While working on inlining non-SQL SRFs [1] I noticed we don't have tests for when a PL/pgSQL
function requires materialize mode but doesn't have a result TupleDesc. Here is a patch adding tests
for that, as well as some other conditions around SRF calls with `SETOF RECORD` vs `TABLE (...)`.
There aren't any code changes, just some new tests.
But IMO it might be better to change the code. This error message is a bit
confusing:
+-- materialize mode requires a result TupleDesc:
+select array_to_set2(array['one', 'two']); -- fail
+ERROR: materialize mode required, but it is not allowed in this context
+CONTEXT: PL/pgSQL function array_to_set2(anyarray) line 3 at RETURN QUERY
Perhaps it would be better to give the same error as here?:
+select * from array_to_set2(array['one', 'two']); -- fail
+ERROR: a column definition list is required for functions returning "record"
+LINE 1: select * from array_to_set2(array['one', 'two']);
If folks agree, I can work on a patch for that. Otherwise, at least this patch documents the current
behavior and increases coverage.
[1] https://commitfest.postgresql.org/49/5083/
Yours,
--
Paul ~{:-)
p...@illuminatedcomputing.comFrom 763db868d2dc121f8745ba9f90e4f737c135bcaa Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <p...@illuminatedcomputing.com>
Date: Wed, 28 Aug 2024 19:44:02 -0700
Subject: [PATCH v1] Add tests for PL/pgSQL Set-Returning Functions
These tests exercize SRFs with and without a result TupleDesc (in other
words RETURNS TABLE (...) vs RETURNS SETOF RECORD). We can only support
materialize mode in the former case. On the other hand, that case
rejects a column definition list as redundant. The position of these
tests shows the contrast with SQL functions (tested above), which
support SETOF RECORD in more places.
---
src/test/regress/expected/rangefuncs.out | 90 ++++++++++++++++++++++++
src/test/regress/sql/rangefuncs.sql | 42 +++++++++++
2 files changed, 132 insertions(+)
diff --git a/src/test/regress/expected/rangefuncs.out b/src/test/regress/expected/rangefuncs.out
index 397a8b35d6d..3d3ebd17780 100644
--- a/src/test/regress/expected/rangefuncs.out
+++ b/src/test/regress/expected/rangefuncs.out
@@ -2169,6 +2169,96 @@ LINE 1: select * from sin(3) as t(f1 int8,f2 int8);
drop type rngfunc_type cascade;
NOTICE: drop cascades to function testrngfunc()
--
+-- test use of PL/pgSQL functions returning setof record
+--
+create or replace function array_to_set2(anyarray) returns setof record as $$
+ begin
+ return query execute 'select i AS "index", $1[i] AS "value" from generate_subscripts($1, 1) i'
+ using $1;
+ end;
+$$ language plpgsql immutable;
+-- materialize mode requires a result TupleDesc:
+select array_to_set2(array['one', 'two']); -- fail
+ERROR: materialize mode required, but it is not allowed in this context
+CONTEXT: PL/pgSQL function array_to_set2(anyarray) line 3 at RETURN QUERY
+select * from array_to_set2(array['one', 'two']) as t(f1 int,f2 text);
+ f1 | f2
+----+-----
+ 1 | one
+ 2 | two
+(2 rows)
+
+select * from array_to_set2(array['one', 'two']); -- fail
+ERROR: a column definition list is required for functions returning "record"
+LINE 1: select * from array_to_set2(array['one', 'two']);
+ ^
+select * from array_to_set2(array['one', 'two']) as t(f1 numeric(4,2),f2 text); -- fail
+ERROR: structure of query does not match function result type
+DETAIL: Returned type integer does not match expected type numeric(4,2) in column 1.
+CONTEXT: SQL statement "select i AS "index", $1[i] AS "value" from generate_subscripts($1, 1) i"
+PL/pgSQL function array_to_set2(anyarray) line 3 at RETURN QUERY
+select * from array_to_set2(array['one', 'two']) as t(f1 point,f2 text); -- fail
+ERROR: structure of query does not match function result type
+DETAIL: Returned type integer does not match expected type point in column 1.
+CONTEXT: SQL statement "select i AS "index", $1[i] AS "value" from generate_subscripts($1, 1) i"
+PL/pgSQL function array_to_set2(anyarray) line 3 at RETURN QUERY
+explain (verbose, costs off)
+ select * from array_to_set2(array['one', 'two']) as t(f1 int, f2 text);
+ QUERY PLAN
+-----------------------------------------------------
+ Function Scan on public.array_to_set2 t
+ Output: f1, f2
+ Function Call: array_to_set2('{one,two}'::text[])
+(3 rows)
+
+drop function array_to_set2;
+--
+-- test use of PL/pgSQL functions returning table
+--
+create or replace function array_to_set2(anyarray) returns table(index int, value anyelement) as $$
+ begin
+ return query execute 'select i AS "index", $1[i] AS "value" from generate_subscripts($1, 1) i'
+ using $1;
+ end;
+$$ language plpgsql immutable;
+-- materialize mode requires a result TupleDesc:
+select array_to_set2(array['one', 'two']);
+ array_to_set2
+---------------
+ (1,one)
+ (2,two)
+(2 rows)
+
+select * from array_to_set2(array['one', 'two']) as t(f1 int,f2 text); -- fail
+ERROR: a column definition list is redundant for a function with OUT parameters
+LINE 1: ...ct * from array_to_set2(array['one', 'two']) as t(f1 int,f2 ...
+ ^
+select * from array_to_set2(array['one', 'two']);
+ index | value
+-------+-------
+ 1 | one
+ 2 | two
+(2 rows)
+
+select * from array_to_set2(array['one', 'two']) as t(f1 numeric(4,2),f2 text); -- fail
+ERROR: a column definition list is redundant for a function with OUT parameters
+LINE 1: ...ct * from array_to_set2(array['one', 'two']) as t(f1 numeric...
+ ^
+select * from array_to_set2(array['one', 'two']) as t(f1 point,f2 text); -- fail
+ERROR: a column definition list is redundant for a function with OUT parameters
+LINE 1: ...ct * from array_to_set2(array['one', 'two']) as t(f1 point,f...
+ ^
+explain (verbose, costs off)
+ select * from array_to_set2(array['one', 'two']);
+ QUERY PLAN
+-----------------------------------------------------
+ Function Scan on public.array_to_set2
+ Output: index, value
+ Function Call: array_to_set2('{one,two}'::text[])
+(3 rows)
+
+drop function array_to_set2;
+--
-- Check some cases involving added/dropped columns in a rowtype result
--
create temp table users (userid text, seq int, email text, todrop bool, moredrop int, enabled bool);
diff --git a/src/test/regress/sql/rangefuncs.sql b/src/test/regress/sql/rangefuncs.sql
index 3c47c98e113..a898ac116de 100644
--- a/src/test/regress/sql/rangefuncs.sql
+++ b/src/test/regress/sql/rangefuncs.sql
@@ -647,6 +647,48 @@ select * from sin(3) as t(f1 int8,f2 int8); -- fail, scalar result type
drop type rngfunc_type cascade;
+--
+-- test use of PL/pgSQL functions returning setof record
+--
+
+create or replace function array_to_set2(anyarray) returns setof record as $$
+ begin
+ return query execute 'select i AS "index", $1[i] AS "value" from generate_subscripts($1, 1) i'
+ using $1;
+ end;
+$$ language plpgsql immutable;
+
+-- materialize mode requires a result TupleDesc:
+select array_to_set2(array['one', 'two']); -- fail
+select * from array_to_set2(array['one', 'two']) as t(f1 int,f2 text);
+select * from array_to_set2(array['one', 'two']); -- fail
+select * from array_to_set2(array['one', 'two']) as t(f1 numeric(4,2),f2 text); -- fail
+select * from array_to_set2(array['one', 'two']) as t(f1 point,f2 text); -- fail
+explain (verbose, costs off)
+ select * from array_to_set2(array['one', 'two']) as t(f1 int, f2 text);
+drop function array_to_set2;
+
+--
+-- test use of PL/pgSQL functions returning table
+--
+
+create or replace function array_to_set2(anyarray) returns table(index int, value anyelement) as $$
+ begin
+ return query execute 'select i AS "index", $1[i] AS "value" from generate_subscripts($1, 1) i'
+ using $1;
+ end;
+$$ language plpgsql immutable;
+
+-- materialize mode requires a result TupleDesc:
+select array_to_set2(array['one', 'two']);
+select * from array_to_set2(array['one', 'two']) as t(f1 int,f2 text); -- fail
+select * from array_to_set2(array['one', 'two']);
+select * from array_to_set2(array['one', 'two']) as t(f1 numeric(4,2),f2 text); -- fail
+select * from array_to_set2(array['one', 'two']) as t(f1 point,f2 text); -- fail
+explain (verbose, costs off)
+ select * from array_to_set2(array['one', 'two']);
+drop function array_to_set2;
+
--
-- Check some cases involving added/dropped columns in a rowtype result
--
--
2.45.0
