<html><head></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; "><div>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.</div><div><br></div><div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal 'Lucida Grande'; ">ERROR: missing FROM-clause entry for table "tempgeo"</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal 'Lucida Grande'; ">LINE 1: SELECT $1 ||'{"x":'|| $2 ||',"y":'||tempgeo.they||'}'</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal 'Lucida Grande'; "> ^</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal 'Lucida Grande'; ">QUERY: SELECT $1 ||'{"x":'|| $2 ||',"y":'||tempgeo.they||'}'</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal 'Lucida Grande'; ">CONTEXT: PL/pgSQL function "pointsfromgeom" line 16 at assignment</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal 'Lucida Grande'; min-height: 15px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal 'Lucida Grande'; ">********** Error **********</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal 'Lucida Grande'; min-height: 15px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal 'Lucida Grande'; ">ERROR: missing FROM-clause entry for table "tempgeo"</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal 'Lucida Grande'; ">SQL state: 42P01</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal 'Lucida Grande'; ">Context: PL/pgSQL function "pointsfromgeom" line 16 at assignment</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal 'Lucida Grande'; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal 'Lucida Grande'; ">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? </div></div><div><br></div><div><div>CREATE OR REPLACE FUNCTION pointsfromgeom(ingeom GEOMETRY) RETURNS TEXT AS $$</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>DECLARE</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>geotype TEXT;</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>output TEXT:='';</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>BEGIN</div><div><span class="Apple-tab-span" style="white-space:pre"> SELECT INTO geotype geometrytype(ingeom);</span></div><div><span class="Apple-tab-span" style="white-space:pre"> </span>IF (geotype = 'MULTIPOINT') THEN</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>DECLARE</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>tmpgeo RECORD;</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>count integer :=1;</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>BEGIN</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>FOR tmpgeo IN SELECT DISTINCT st_x((st_dump(ingeom)).geom) as thex,st_y((st_dump(ingeom)).geom) as they</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>LOOP </div><div><span class="Apple-tab-span" style="white-space:pre"> </span>IF (count=1) THEN</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>output := '[{"x":'||tmpgeo.thex||',"y":'||tempgeo.they||'}';</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>ELSE</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>output := output||',{"x":'||tmpgeo.thex||',"y":'||tempgeo.they||'}';</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>END IF;</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>END LOOP;</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>output:=output||']';</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>END;</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>ELSE</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>RAISE NOTICE 'Geometry type is not compatable';</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>RETURN NULL;</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>END IF;</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>RETURN output;</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>END;</div><div>$$ LANGUAGE 'plpgsql';</div></div><div><br></div><div><br>Thanks,</div><div><br class="webkit-block-placeholder"></div><div>Paul</div>
<br></body></html>