Thread: Problem using set-returning functions
Hi, I'm cross-posting this to the plpgsql list: We've run into a small problem creating a set-returning function for PostGIS in PostgreSQL 8.1.0: CREATE OR REPLACE FUNCTION generate_x (geom geometry) RETURNS SETOF double precision AS 'DECLARE index integer; BEGIN FOR index IN 1 .. npoints(geom) LOOPRETURN NEXT X(geometryn(geom,index)); END LOOP; END ' LANGUAGE 'plpgsql' IMMUTABLE STRICT; Now, trying to use this function yields the following error: navteq=# select foo,generate_x(bar) from test; ERROR: set-valued function called in context that cannot accept a set CONTEXT: PL/pgSQL function "generate_x" line 5 at return next However, it is fine to call other set returning functions in the same context: navteq=# select foo,dump(bar) from test;foo | dump -----+-------------------------------------------------- 42 | ({1},0101000000000000000000F03F0000000000000040) 42 | ({2},010100000000000000000008400000000000001040)42 | ({3},010100000000000000000014400000000000001840) 23 | ({1},01010000000000000000001C400000000000002040) (4 rows) navteq=# select foo,x(geometryn(bar,generate_series(1,npoints(bar)))) FROM test;foo | x -----+--- 42 | 1 42 | 3 42 | 5 23 | 7 (4 rows) (This third query is equal to what I expected the failing query to do.) The table "test" looks as follows; navteq=# \d test Table "public.test"Column | Type | Modifiers --------+----------+-----------foo | integer |bar | geometry | navteq=# select foo,asText(bar) from test;foo | astext -----+------------------------- 42 | MULTIPOINT(1 2,3 4,5 6) 23 | MULTIPOINT(7 8) (2 rows) I'm shure its a small detail I've blindly ignored, but I'm stuck ATM. Thanks, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
On Mar 27, 2006, at 5:41 AM, Markus Schaber wrote: > navteq=# select foo,generate_x(bar) from test; > ERROR: set-valued function called in context that cannot accept a set > CONTEXT: PL/pgSQL function "generate_x" line 5 at return next > > However, it is fine to call other set returning functions in the same > context: With SRFs, you need to specify what you want to select. In other words if you are calling generate_x(bar) you need "select * from generate_x(bar)" -- "select generate_x(bar)" will not work. So for your query I think you need something like: select foo, (select x from generate_x(bar)) from test; John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL
Hi, John, John DeSoi wrote: > With SRFs, you need to specify what you want to select. In other words > if you are calling generate_x(bar) you need "select * from > generate_x(bar)" -- "select generate_x(bar)" will not work. So, then, why does it work with generate_series() and dump()? Confused, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
Hi Markus, On Mar 27, 2006, at 9:00 AM, Markus Schaber wrote: > So, then, why does it work with generate_series() and dump()? I'm not sure. All I know is I spent a while the other day puzzling over the same error message you had and finally realized I had to add a select expression to fix it. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL
On Mon, 27 Mar 2006, Markus Schaber wrote: > Hi, John, > > John DeSoi wrote: > > > With SRFs, you need to specify what you want to select. In other words > > if you are calling generate_x(bar) you need "select * from > > generate_x(bar)" -- "select generate_x(bar)" will not work. > > So, then, why does it work with generate_series() and dump()? It's an implementation detail. Some languages handle SRFs in a way that can be handled in the select list (SQL and C I think) and others do not (plpgsql). The latter will likely change at some point, although there are some confusing issues with SRFs in the select list as well, see the difference in behavior between: select generate_series(1,10), generate_series(1,5);vs select * from generate_series(1,10) g1, generate_series(1,5) g2;
Hi, Stephan, Stephan Szabo wrote: > It's an implementation detail. Some languages handle SRFs in a way that > can be handled in the select list (SQL and C I think) and others do not > (plpgsql). Ah, that's an enlightening explanation, thanks. > The latter will likely change at some point, although there are > some confusing issues with SRFs in the select list as well, see the > difference in behavior between: > > select generate_series(1,10), generate_series(1,5); > vs > select * from generate_series(1,10) g1, generate_series(1,5) g2; I know that the SRF special semantics are ugly, and would vote for adjacent tables to be implemented as replacement. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
Markus Schaber <schabi@logix-tt.com> writes: > navteq=# select foo,generate_x(bar) from test; > ERROR: set-valued function called in context that cannot accept a set > CONTEXT: PL/pgSQL function "generate_x" line 5 at return next plpgsql SRFs don't support being called from the SELECT target list, only from a FROM-expression. Your other function is probably written in SQL not plpgsql; SQL functions do support this. plpgsql could probably be fixed if we were really motivated to do so, but I think most people who have looked at it feel we should phase out the capability to call SRFs from a target list, rather than extend it. It's weird and not very semantically sound --- in particular, there's no very sensible definition if there's more than one of them in the target list. See past discussions in the PG archives. regards, tom lane