[postgis-users] Using plpgsql for in select with st_dump

[Moen, Paul T.]

I am trying to expand my horizons and learn some plpgsq.  I want to split out a list of points from a multipoint via plpgsql into json, not geojson.  I get the following notices.

ERROR:  missing FROM-clause entry for table "tempgeo"
LINE 1: SELECT   $1 ||'{"x":'|| $2 ||',"y":'||tempgeo.they||'}'
                                              ^
QUERY:  SELECT   $1 ||'{"x":'|| $2 ||',"y":'||tempgeo.they||'}'
CONTEXT:  PL/pgSQL function "pointsfromgeom" line 16 at assignment

********** Error **********

ERROR: missing FROM-clause entry for table "tempgeo"
SQL state: 42P01
Context: PL/pgSQL function "pointsfromgeom" line 16 at assignment

Is there any way to do this? Does the record type have to be based on an existing table?  Is this the right list to ask?

CREATE OR REPLACE FUNCTION pointsfromgeom(ingeom GEOMETRY) RETURNS TEXT AS $$
DECLARE
geotype TEXT;
output TEXT:='';
BEGIN
SELECT INTO geotype geometrytype(ingeom);
IF (geotype = 'MULTIPOINT') THEN
DECLARE
tmpgeo RECORD;
count integer :=1;
BEGIN
FOR tmpgeo IN SELECT DISTINCT st_x((st_dump(ingeom)).geom) as thex,st_y((st_dump(ingeom)).geom) as they
LOOP
IF (count=1) THEN
output := '[{"x":'||tmpgeo.thex||',"y":'||tempgeo.they||'}';
ELSE
output := output||',{"x":'||tmpgeo.thex||',"y":'||tempgeo.they||'}';
END IF;
END LOOP;
output:=output||']';
END;
ELSE
RAISE NOTICE 'Geometry type is not compatable';
RETURN NULL;
END IF;
RETURN output;
END;
$$ LANGUAGE 'plpgsql';


Thanks,

Paul

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20091106/b21ffd15/attachment.html>