pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedProblem using set-returning functions
9+ messages / 4 participants
[nested] [flat]
* Problem using set-returning functions
@ 2006-03-27 10:41 Markus Schaber <schabi@logix-tt.com>
0 siblings, 2 replies; 9+ messages in thread
From: Markus Schaber @ 2006-03-27 10:41 UTC (permalink / raw)
To: PostGIS Users Discussion <postgis-users@postgis.refractions.net>; pgsql-sql
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) LOOP
RETURN 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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [postgis-users] Problem using set-returning functions
@ 2006-03-27 13:21 Markus Schaber <schabi@logix-tt.com>
0 siblings, 0 replies; 9+ messages in thread
From: Markus Schaber @ 2006-03-27 13:21 UTC (permalink / raw)
To: PostGIS Users Discussion <postgis-users@postgis.refractions.net>; +Cc: pgsql-sql
Hi, Regina,
Obe, Regina DND\MIS wrote:
> generate_series works I think because it is a special case - it isn't
> really a set function even though it behaves that way sortof.
Well, dump() works as well, and it is no build-in.
As a recent thread here stated, it is how set returning functions are
expected to work as long as "adjacent tables" are not implemented.
http://www.mail-archive.com/pgsql-sql@postgresql.org/msg20545.html
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Problem using set-returning functions
@ 2006-03-27 13:32 John DeSoi <desoi@pgedit.com>
parent: Markus Schaber <schabi@logix-tt.com>
1 sibling, 1 reply; 9+ messages in thread
From: John DeSoi @ 2006-03-27 13:32 UTC (permalink / raw)
To: Markus Schaber <schabi@logix-tt.com>; +Cc: PostGIS Users Discussion <postgis-users@postgis.refractions.net>; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Problem using set-returning functions
@ 2006-03-27 14:00 Markus Schaber <schabi@logix-tt.com>
parent: John DeSoi <desoi@pgedit.com>
0 siblings, 2 replies; 9+ messages in thread
From: Markus Schaber @ 2006-03-27 14:00 UTC (permalink / raw)
To: John DeSoi <desoi@pgedit.com>; +Cc: PostGIS Users Discussion <postgis-users@postgis.refractions.net>; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [postgis-users] Problem using set-returning functions
@ 2006-03-27 14:29 Markus Schaber <schabi@logix-tt.com>
0 siblings, 0 replies; 9+ messages in thread
From: Markus Schaber @ 2006-03-27 14:29 UTC (permalink / raw)
To: PostGIS Users Discussion <postgis-users@postgis.refractions.net>; +Cc: pgsql-sql
Hi, Regina,
Obe, Regina DND\MIS wrote:
> Actually I suspect no set returning function created in pgsql will work
> the way you are trying to do it. Not sure why. The dump is done in c
> and plugged in and I think it explicitly generates a tuple in the target
> set for every output.
>
> Try rewriting your function to something like this and see if it works
>
> CREATE OR REPLACE FUNCTION generate_x (geom geometry)
> RETURNS SETOF double precision AS
> '
> SELECT X(geometryn($1,index))
> FROM generate_series(1, npoints($1)) index;
>
> ' LANGUAGE 'sql' IMMUTABLE STRICT;
Yes, it seems to work. This will solve the OPs case.
But it is still a good question whether it is possible to accomplish
this using plpgsql.
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Problem using set-returning functions
@ 2006-03-27 14:30 John DeSoi <desoi@pgedit.com>
parent: Markus Schaber <schabi@logix-tt.com>
1 sibling, 0 replies; 9+ messages in thread
From: John DeSoi @ 2006-03-27 14:30 UTC (permalink / raw)
To: Markus Schaber <schabi@logix-tt.com>; +Cc: PostGIS Users Discussion <postgis-users@postgis.refractions.net>; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Problem using set-returning functions
@ 2006-03-27 15:06 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Markus Schaber <schabi@logix-tt.com>
1 sibling, 1 reply; 9+ messages in thread
From: Stephan Szabo @ 2006-03-27 15:06 UTC (permalink / raw)
To: Markus Schaber <schabi@logix-tt.com>; +Cc: John DeSoi <desoi@pgedit.com>; PostGIS Users Discussion <postgis-users@postgis.refractions.net>; pgsql-sql
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;
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Problem using set-returning functions
@ 2006-03-27 15:14 Markus Schaber <schabi@logix-tt.com>
parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 0 replies; 9+ messages in thread
From: Markus Schaber @ 2006-03-27 15:14 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: PostGIS Users Discussion <postgis-users@postgis.refractions.net>; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Problem using set-returning functions
@ 2006-03-27 15:48 Tom Lane <tgl@sss.pgh.pa.us>
parent: Markus Schaber <schabi@logix-tt.com>
1 sibling, 0 replies; 9+ messages in thread
From: Tom Lane @ 2006-03-27 15:48 UTC (permalink / raw)
To: Markus Schaber <schabi@logix-tt.com>; +Cc: PostGIS Users Discussion <postgis-users@postgis.refractions.net>; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2006-03-27 15:48 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-27 10:41 Problem using set-returning functions Markus Schaber <schabi@logix-tt.com>
2006-03-27 13:32 ` John DeSoi <desoi@pgedit.com>
2006-03-27 14:00 ` Markus Schaber <schabi@logix-tt.com>
2006-03-27 14:30 ` John DeSoi <desoi@pgedit.com>
2006-03-27 15:06 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2006-03-27 15:14 ` Markus Schaber <schabi@logix-tt.com>
2006-03-27 15:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-03-27 13:21 Re: [postgis-users] Problem using set-returning functions Markus Schaber <schabi@logix-tt.com>
2006-03-27 14:29 Re: [postgis-users] Problem using set-returning functions Markus Schaber <schabi@logix-tt.com>
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