Mike, In PostgreSQL 8.4 by the way, what you have will work. For lower versions I wrap my plpgsql in an sql function wrapper as described here.
http://www.postgresonline.com/journal/index.php?/archives/16-Trojan-SQL- Function-Hack-A-PL-Lemma-in-Disguise.html Hope that helps, Regina -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Michael Toews Sent: Wednesday, May 27, 2009 4:46 PM To: [email protected] Subject: [postgis-users] Feeding geometries into a custom SRF query Hi, I'm blanking out on how to write the SQL query for this. I have a custom ST_DumpPoints(geometry) set returning function (SRF) written in PL/pgSQL that returns a set of geometry_dump items[1]. Since it is not written in C/SQL, I cannot use the SRF like I can with ST_Dump or generate_series of the form: SELECT (ST_Dump(geometry)).geom FROM mytable; .. works to extract the geom from the geometry_dump data type. Instead, using my PL/pgSQL function: SELECT (ST_DumpPoints(geometry)).geom FROM mytable; ERROR: set-valued function called in context that cannot accept a set For some undocumented reason, PostgreSQL cannot use a SRF written in plpgsql on the left-side of "FROM". Only SRF written in sql or c can do this[2]. I am instead forced to rewrite the SQL query in the form SELECT * FROM mysrf(value), or in my case: SELECT * FROM ST_DumpPoints('MULTIPOLYGON(((-0.57 0.46,-0.85 0.08,0 0.03,-0.57 0.46)),((-0.52 0.02,-0.66 -0.04,-0.46 -0.18,-0.52 0.02)))'::geometry); or something similar where I need to type the geometry in after the "FROM" part. How can I rewrite this SQL statement so I'm selecting the geometry into the SRF on the right-side of the "FROM" part? I can't seem write this SQL query, and I don't want to copy/paste the geometry for each object. Thanks in advance. -Mike [1] This half-thought-out function assumes multipolygon geometry; only uses boundary linestring (no inner rings); path::integer[] has the base-1 info format {geometry_number, point_number}. There is probably a better way to write this, but I'm not sure how. If written in pure sql, I would be able to query using the SRF on the left of FROM making the SQL easier to write. Here is what I have: CREATE OR REPLACE FUNCTION ST_DumpPoints(geometry) RETURNS SETOF geometry_dump AS $BODY$DECLARE m integer; g geometry; n integer; p geometry_dump%rowtype; BEGIN FOR m IN SELECT generate_series(1, ST_NumGeometries($1)) LOOP p.path[1] := m; -- use to store Multipolygon number g := ST_Boundary(ST_GeometryN($1, m)); FOR n IN SELECT generate_series(1, ST_NumPoints(g) - 1) LOOP p.path[2] := n; -- use to store Point number p.geom := ST_PointN(g, n); RETURN NEXT p; END LOOP; END LOOP; RETURN; END;$BODY$ LANGUAGE 'plpgsql' IMMUTABLE STRICT COST 100 ROWS 1000; -- Test it (with compliant SRF on right of "FROM"): SELECT path, astext(geom) FROM ST_DumpPoints('MULTIPOLYGON(((-0.57 0.46,-0.85 0.08,0 0.03,-0.57 0.46)),((-0.52 0.02,-0.66 -0.04,-0.46 -0.18,-0.52 0.02)))'::geometry); [2] Background discussion on PG issues with SRF with non c/sql functions is here: http://postgis.refractions.net/pipermail/postgis-users/2006-March/thread .html#11568 _______________________________________________ postgis-users mailing list [email protected] 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. _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
