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

[Kevin Neufeld]

You have a typo in both of your "output := ..." lines.  You've declared 
tmpgeo, not tempgeo.
Also, you're missing a line to increment count.  Perhaps after the inner 
if statement, "count := count+1;"
Hope that helps,
Kevin

Moen, Paul T. wrote:
> 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
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>