[postgis-users] Problem using set-returning functions

[Obe, Regina DND\MIS]

I think your generate_x has to be in the from as since it returns a set
I don't see how what you have could work since you are using it to
return a field in the below and it is returning a set.  I apologize if
the above sounds convoluted.

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.

For examle try

select x.* FROM generate_x((SELECT collect(bar) FROM test)) x 

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] 
Sent: Monday, March 27, 2006 5:42 AM
To: PostGIS Users Discussion; PostgreSQL SQL List
Subject: [postgis-users] 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) 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
_______________________________________________
postgis-users mailing list postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally
privileged and/or exempt from disclosure pursuant to Massachusetts
law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and
delete the material from any computer.