pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feed8.4beta[12] set returning function fails -- was O.K. with 8.3
3+ messages / 3 participants
[nested] [flat]
* 8.4beta[12] set returning function fails -- was O.K. with 8.3
@ 2009-06-10 23:58 J. Greg Davidson <jgd@well.com>
0 siblings, 1 reply; 3+ messages in thread
From: J. Greg Davidson @ 2009-06-10 23:58 UTC (permalink / raw)
To: pgsql-hackers; +Cc: 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
CREATE OR REPLACE
FUNCTION array_to_list(ANYARRAY) RETURNS SETOF ANYELEMENT AS $$
SELECT $1[i] FROM generate_subscripts($1, 1) i
$$ LANGUAGE SQL STRICT IMMUTABLE;
COMMENT ON FUNCTION array_to_list(ANYARRAY) IS
'returns the array as a set of its elements from lowest to highest;
- can we guarantee the values will be seen in order???';
SELECT array_to_list(ARRAY['one', 'two']);
-- Works great, vis:
--
-- array_to_list
-- ---------------
-- one
-- two
-- (2 rows)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: 8.4beta[12] set returning function fails -- was O.K. with 8.3
@ 2009-06-11 12:43 Hitoshi Harada <umi.tanuki@gmail.com>
parent: J. Greg Davidson <jgd@well.com>
0 siblings, 1 reply; 3+ messages in thread
From: Hitoshi Harada @ 2009-06-11 12:43 UTC (permalink / raw)
To: J. Greg Davidson <jgd@well.com>; +Cc: pgsql-hackers
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:
inline patch 1: src/backend/executor/functions.c (+1/-2 lines) download inline-1.patch
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
* Re: 8.4beta[12] set returning function fails -- was O.K. with 8.3
@ 2009-06-11 17:28 Tom Lane <tgl@sss.pgh.pa.us>
parent: Hitoshi Harada <umi.tanuki@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2009-06-11 17:28 UTC (permalink / raw)
To: Hitoshi Harada <umi.tanuki@gmail.com>; +Cc: J. Greg Davidson <jgd@well.com>; pgsql-hackers
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
inline patch 1: src/backend/executor/execQual.c, src/backend/executor/functions.c, src/test/regress/expected/rangefuncs.out and 1 more (+129/-8 lines) download inline-1.patch
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();
end of thread, other threads:[~2009-06-11 17:28 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-06-10 23:58 8.4beta[12] set returning function fails -- was O.K. with 8.3 J. Greg Davidson <jgd@well.com>
2009-06-11 12:43 ` Re: 8.4beta[12] set returning function fails -- was O.K. with 8.3 Hitoshi Harada <umi.tanuki@gmail.com>
2009-06-11 17:28 ` Re: 8.4beta[12] set returning function fails -- was O.K. with 8.3 Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox