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

[Michael Toews]

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