Using LOOP with set returning functions

[Muskie77]

I am struggling to write a pl/sql function that encompasses the two below queries. Right now, the function executes, but when called it isn't returning anything....

It looks like I *might* be running into some trouble with passing $1 in a set returning function (i.e ST_Dump or generate_series...). It fails like this on a Multipolygon feature:

ERROR:  value "01030000000200000005000000000000000000000000000000000000000000000000001040000000000000000000000000000010400000000000001040000000000000000000000000000010400000000000000000000000000000000005000000000000000000F03F000000000000F03F0000000000000040000000000000F03F00000000000000400000000000000040000000000000F03F0000000000000040000000000000F03F000000000000F03F" is out of range for type integer
CONTEXT:  PL/pgSQL function linesfrompolygon2(geometry) line 8 at FOR over SELECT rows
********** Error **********
 

and returns nothing on a polygon feature.



Query 1: Returns A list of Polygons from a Multi or Single part geom

 
    SELECT id, name, 
    ST_GeometryN(the_geom, generate_series(1, ST_NumGeometries(the_geom)))
AS geom
INTO table_of_polys_only
from poly_and_multipoly;
 

Result:

id|name|geom
1|A|POLYGON ((7.7 3.8,7.7 5.8,9 5.8,7.7 3.8))
2|B|POLYGON ((0 0,4 0,4 4,0 4,0 0), (1 1,2 1,2 2,1 2,1 1))
3|B|POLYGON ((-1 -1,-1 -2,-2 -2,-2 -1,-1 -1))
 

Query 2: Breaks the Polygons into Linestrings

SELECT id, name, st_asewkt((st_dump(st_boundary(geom))).geom)
FROM table_of_polys_only;
 

Result:

id|name|st_asewkt text
1|A|LINESTRING (7.7 3.8,7.7 5.8,9 5.8,7.7 3.8)
2|B|LINESTRING (0 0,4 0,4 4,0 4,0 0)
2|B|LINESTRING (1 1,2 1,2 2,1 2,1 1)
2|B|LINESTRING (-1 -1,-1 -2,-2 -2,-2 -1,-1 -1)
 

So far my code looks like this:

CREATE OR REPLACE FUNCTION LinesFromPolygon2(polygon geometry) 
RETURNS SETOF geometry_dump AS 
$BODY$DECLARE 
 m integer; -- integer the stores # of polys in a mpoly
 g geometry;
 p geometry_dump%ROWTYPE; 
BEGIN
IF GeometryType($1) LIKE 'MULTI%' THEN 
-- break multipolygons into polygons
    FOR m IN SELECT ST_GeometryN($1, generate_series(1, ST_NumGeometries($1))) LOOP 
      p.path[1] := m; -- use to store Multipolygon number 
      g := (ST_Dump(ST_Boundary(ST_GeometryN($1, m)))).geom;
     RETURN NEXT p;
     END LOOP;
ELSE -- It is not a MULTI- geometry 
    g := (ST_Dump(ST_Boundary($1))).geom; 
  END IF; 
  RETURN; 
END;$BODY$
LANGUAGE plpgsql ;
 

Call:

SELECT id, name, LinesFromPolygon2(the_geom)
  FROM public.poly_and_multipoly;
 

Trial data (using PostGIS ext)

CREATE TABLE poly_and_multipoly (
  "id" SERIAL NOT NULL PRIMARY KEY,
  "name" char(1) NOT NULL,
  "the_geom" geometry NOT NULL
);
-- add data, A is a polygon, B is a multipolygon
INSERT INTO poly_and_multipoly (name, the_geom) VALUES (
    'A', 'POLYGON((7.7 3.8,7.7 5.8,9.0 5.8,7.7 3.8))'::geometry
    ), (
    'B',
    'MULTIPOLYGON(((0 0,4 0,4 4,0 4,0 0),(1 1,2 1,2 2,1 2,1 1)), ((-1 -1,-1 -2,-2 -2,-2 -1,-1 -1)))'::geometry
);