[postgis-users] Feeding geometries into a custom SRF query

[Obe, Regina]

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: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Michael Toews
Sent: Wednesday, May 27, 2009 4:46 PM
To: postgis-users at postgis.refractions.net
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
postgis-users at postgis.refractions.net
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.