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

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[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@...
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>  

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users