8.4beta[12] set returning function fails -- was O.K. with 8.3

2009/6/11 J. Greg Davidson <jgd@well.com>:

Dear PostgreSQL Hackers,

Through PostgreSQL 8.3, both of the following functions worked, using
 generate_series(array_lower($1, 1), array_upper($1, 1)) i
instead of generate_subscripts($1, 1).

With PostgreSQL 8.4, both are accepted, but only the second one works,
regardless of whether I use generate_subscripts or the old way.  The error
is shown.  What's going on?

Thanks,

_Greg

CREATE OR REPLACE
FUNCTION array_to_set(ANYARRAY) RETURNS SETOF RECORD AS $$
 SELECT i AS "index", $1[i] AS "value" FROM generate_subscripts($1, 1) i
$$ LANGUAGE SQL STRICT IMMUTABLE;
COMMENT ON FUNCTION array_to_set(ANYARRAY) IS
'returns the array as a set of RECORD(index, value) pairs';

SELECT array_to_set(ARRAY['one', 'two']);

-- BREAKS IN PG 8.4 beta1 & beta2, vis:
--
--      ERROR:  0A000: set-valued function called in context that cannot accept
a set
--      CONTEXT:  SQL function "array_to_set" during startup
--      LOCATION:  fmgr_sql, functions.c:644

I grep'ed HEAD and found the following change helps this:

diff --git a/src/backend/executor/functions.c b/src/backend/executor/functions.c
index c0261fe..0882671 100644
--- a/src/backend/executor/functions.c
+++ b/src/backend/executor/functions.c
@@ -637,8 +637,7 @@ fmgr_sql(PG_FUNCTION_ARGS)
                 */
                if (!rsi || !IsA(rsi, ReturnSetInfo) ||
                        (rsi->allowedModes & SFRM_ValuePerCall) == 0 ||
-                       (rsi->allowedModes & SFRM_Materialize) == 0 ||
-                       rsi->expectedDesc == NULL)
+                       (rsi->allowedModes & SFRM_Materialize) == 0)
                        ereport(ERROR,
                                        (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
                                         errmsg("set-valued function
called in context that cannot accept a set")));

I am not completely sure but rsi->expectedDesc check seems not needed
as before. All regression tests passed.

Regards,

--
Hitoshi Harada

Hitoshi Harada <umi.tanuki@gmail.com> writes:

2009/6/11 J. Greg Davidson <jgd@well.com>:

-- BREAKS IN PG 8.4 beta1 & beta2, vis:
-- � � �ERROR: �0A000: set-valued function called in context that cannot accept a set

I am not completely sure but rsi->expectedDesc check seems not needed
as before. All regression tests passed.

Actually that check was protecting some code in execQual.c that would
fall over if the function tried to return a tuplestore. But it turned
out to be pretty simple to fix, so I did so.

regards, tom lane

Index: src/backend/executor/execQual.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/executor/execQual.c,v
retrieving revision 1.249
diff -c -r1.249 execQual.c
*** src/backend/executor/execQual.c	11 Jun 2009 14:48:57 -0000	1.249
--- src/backend/executor/execQual.c	11 Jun 2009 17:14:41 -0000
***************
*** 1089,1097 ****
  			fcache->funcResultDesc = tupdesc;
  			fcache->funcReturnsTuple = false;
  		}
  		else
  		{
! 			/* Else, we will complain if function wants materialize mode */
  			fcache->funcResultDesc = NULL;
  		}

--- 1089,1103 ----
  			fcache->funcResultDesc = tupdesc;
  			fcache->funcReturnsTuple = false;
  		}
+ 		else if (functypclass == TYPEFUNC_RECORD)
+ 		{
+ 			/* This will work if function doesn't need an expectedDesc */
+ 			fcache->funcResultDesc = NULL;
+ 			fcache->funcReturnsTuple = true;
+ 		}
  		else
  		{
! 			/* Else, we will fail if function needs an expectedDesc */
  			fcache->funcResultDesc = NULL;
  		}

***************
*** 1252,1269 ****
if (fcache->funcResultSlot == NULL)
{
/* Create a slot so we can read data out of the tuplestore */
MemoryContext oldcontext;

! /* We must have been able to determine the result rowtype */
! if (fcache->funcResultDesc == NULL)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("function returning setof record called in "
"context that cannot accept type record")));

! oldcontext = MemoryContextSwitchTo(fcache->func.fn_mcxt);
! fcache->funcResultSlot =
! MakeSingleTupleTableSlot(fcache->funcResultDesc);
MemoryContextSwitchTo(oldcontext);
}

--- 1258,1289 ----
  	if (fcache->funcResultSlot == NULL)
  	{
  		/* Create a slot so we can read data out of the tuplestore */
+ 		TupleDesc	slotDesc;
  		MemoryContext oldcontext;

! 		oldcontext = MemoryContextSwitchTo(fcache->func.fn_mcxt);
! 
! 		/*
! 		 * If we were not able to determine the result rowtype from context,
! 		 * and the function didn't return a tupdesc, we have to fail.
! 		 */
! 		if (fcache->funcResultDesc)
! 			slotDesc = fcache->funcResultDesc;
! 		else if (resultDesc)
! 		{
! 			/* don't assume resultDesc is long-lived */
! 			slotDesc = CreateTupleDescCopy(resultDesc);
! 		}
! 		else
! 		{
  			ereport(ERROR,
  					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
  					 errmsg("function returning setof record called in "
  							"context that cannot accept type record")));
+ 			slotDesc = NULL;	/* keep compiler quiet */
+ 		}

! fcache->funcResultSlot = MakeSingleTupleTableSlot(slotDesc);
MemoryContextSwitchTo(oldcontext);
}

Index: src/backend/executor/functions.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/executor/functions.c,v
retrieving revision 1.134
diff -c -r1.134 functions.c
*** src/backend/executor/functions.c	11 Jun 2009 14:48:57 -0000	1.134
--- src/backend/executor/functions.c	11 Jun 2009 17:14:41 -0000
***************
*** 634,644 ****
  		 * For simplicity, we require callers to support both set eval modes.
  		 * There are cases where we must use one or must use the other, and
  		 * it's not really worthwhile to postpone the check till we know.
  		 */
  		if (!rsi || !IsA(rsi, ReturnSetInfo) ||
  			(rsi->allowedModes & SFRM_ValuePerCall) == 0 ||
! 			(rsi->allowedModes & SFRM_Materialize) == 0 ||
! 			rsi->expectedDesc == NULL)
  			ereport(ERROR,
  					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
  					 errmsg("set-valued function called in context that cannot accept a set")));
--- 634,644 ----
  		 * For simplicity, we require callers to support both set eval modes.
  		 * There are cases where we must use one or must use the other, and
  		 * it's not really worthwhile to postpone the check till we know.
+ 		 * But note we do not require caller to provide an expectedDesc.
  		 */
  		if (!rsi || !IsA(rsi, ReturnSetInfo) ||
  			(rsi->allowedModes & SFRM_ValuePerCall) == 0 ||
! 			(rsi->allowedModes & SFRM_Materialize) == 0)
  			ereport(ERROR,
  					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
  					 errmsg("set-valued function called in context that cannot accept a set")));
Index: src/test/regress/expected/rangefuncs.out
===================================================================
RCS file: /cvsroot/pgsql/src/test/regress/expected/rangefuncs.out,v
retrieving revision 1.21
diff -c -r1.21 rangefuncs.out
*** src/test/regress/expected/rangefuncs.out	30 Mar 2009 04:08:43 -0000	1.21
--- src/test/regress/expected/rangefuncs.out	11 Jun 2009 17:14:42 -0000
***************
*** 763,765 ****
--- 763,832 ----
  (1 row)

  drop function foo1(n integer);
+ -- test use of SQL functions returning record
+ -- this is supported in some cases where the query doesn't specify
+ -- the actual record type ...
+ create function array_to_set(anyarray) returns setof record as $$
+   select i AS "index", $1[i] AS "value" from generate_subscripts($1, 1) i
+ $$ language sql strict immutable;
+ select array_to_set(array['one', 'two']);
+  array_to_set 
+ --------------
+  (1,one)
+  (2,two)
+ (2 rows)
+ 
+ select * from array_to_set(array['one', 'two']) as t(f1 int,f2 text);
+  f1 | f2  
+ ----+-----
+   1 | one
+   2 | two
+ (2 rows)
+ 
+ select * from array_to_set(array['one', 'two']); -- fail
+ ERROR:  a column definition list is required for functions returning "record"
+ LINE 1: select * from array_to_set(array['one', 'two']);
+                       ^
+ create temp table foo(f1 int8, f2 int8);
+ create function testfoo() returns record as $$
+   insert into foo values (1,2) returning *;
+ $$ language sql;
+ select testfoo();
+  testfoo 
+ ---------
+  (1,2)
+ (1 row)
+ 
+ select * from testfoo() as t(f1 int8,f2 int8);
+  f1 | f2 
+ ----+----
+   1 |  2
+ (1 row)
+ 
+ select * from testfoo(); -- fail
+ ERROR:  a column definition list is required for functions returning "record"
+ LINE 1: select * from testfoo();
+                       ^
+ drop function testfoo();
+ create function testfoo() returns setof record as $$
+   insert into foo values (1,2), (3,4) returning *;
+ $$ language sql;
+ select testfoo();
+  testfoo 
+ ---------
+  (1,2)
+  (3,4)
+ (2 rows)
+ 
+ select * from testfoo() as t(f1 int8,f2 int8);
+  f1 | f2 
+ ----+----
+   1 |  2
+   3 |  4
+ (2 rows)
+ 
+ select * from testfoo(); -- fail
+ ERROR:  a column definition list is required for functions returning "record"
+ LINE 1: select * from testfoo();
+                       ^
+ drop function testfoo();
Index: src/test/regress/sql/rangefuncs.sql
===================================================================
RCS file: /cvsroot/pgsql/src/test/regress/sql/rangefuncs.sql,v
retrieving revision 1.10
diff -c -r1.10 rangefuncs.sql
*** src/test/regress/sql/rangefuncs.sql	30 Mar 2009 04:08:43 -0000	1.10
--- src/test/regress/sql/rangefuncs.sql	11 Jun 2009 17:14:42 -0000
***************
*** 351,353 ****
--- 351,387 ----
  select t.a, t, t.a from foo1(10000) t limit 1;

  drop function foo1(n integer);
+ 
+ -- test use of SQL functions returning record
+ -- this is supported in some cases where the query doesn't specify
+ -- the actual record type ...
+ 
+ create function array_to_set(anyarray) returns setof record as $$
+   select i AS "index", $1[i] AS "value" from generate_subscripts($1, 1) i
+ $$ language sql strict immutable;
+ 
+ select array_to_set(array['one', 'two']);
+ select * from array_to_set(array['one', 'two']) as t(f1 int,f2 text);
+ select * from array_to_set(array['one', 'two']); -- fail
+ 
+ create temp table foo(f1 int8, f2 int8);
+ 
+ create function testfoo() returns record as $$
+   insert into foo values (1,2) returning *;
+ $$ language sql;
+ 
+ select testfoo();
+ select * from testfoo() as t(f1 int8,f2 int8);
+ select * from testfoo(); -- fail
+ 
+ drop function testfoo();
+ 
+ create function testfoo() returns setof record as $$
+   insert into foo values (1,2), (3,4) returning *;
+ $$ language sql;
+ 
+ select testfoo();
+ select * from testfoo() as t(f1 int8,f2 int8);
+ select * from testfoo(); -- fail
+ 
+ drop function testfoo();