Обсуждение: 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