Iterate and write separately linestrings (generate_series)

Question

I have a table with many polygons and multipolygons, and I would like to run one function to split them into LineStrings (or MultiLinestrings).

My problem is currently I am returning a set with ... no geometry (?) ...

Currently, the function looks like this ... (ending based on the help of Mike T.)

CREATE OR REPLACE FUNCTION LinesFromPoly2(polygon geometry) 
RETURNS SETOF geometry_dump AS 
$BODY$DECLARE 
 m integer; 
 g geometry;
 p geometry_dump%ROWTYPE; 
BEGIN
    FOR m IN SELECT generate_series(1, ST_NumGeometries($1)) LOOP 
      p.path[1] := m;
      p.geom := ST_Boundary(ST_GeometryN($1, m));
     RETURN NEXT p;
     END LOOP;
  RETURN; 
END;$BODY$
LANGUAGE plpgsql ;

CALL:

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

RETURN:

1|A|({1},)
2|B|({1},)
2|B|({2},)

SAMPLES DATA:

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
);

Answer 1

You do not need a custom function to do what you want. For example, just try to get access to two elements ST_Dump( pathand geom):

SELECT id, name,
  (ST_Dump(ST_Boundary(the_geom))).path[1],
  ST_AsText((ST_Dump(ST_Boundary(the_geom))).geom)
FROM poly_and_multipoly;

 id | name | path |                 st_astext
----+------+------+-------------------------------------------
  1 | A    |      | LINESTRING(7.7 3.8,7.7 5.8,9 5.8,7.7 3.8)
  2 | B    |    1 | LINESTRING(0 0,4 0,4 4,0 4,0 0)
  2 | B    |    2 | LINESTRING(1 1,2 1,2 2,1 2,1 1)
  2 | B    |    3 | LINESTRING(-1 -1,-1 -2,-2 -2,-2 -1,-1 -1)
(4 rows)

Or one [MULTI] LINESTRING for each geometric part:

SELECT id, name,
  (ST_Dump(the_geom)).path[1],
  ST_AsText(ST_Boundary((ST_Dump(the_geom)).geom))
FROM poly_and_multipoly;

 id | name | path |                          st_astext
----+------+------+--------------------------------------------------------------
  1 | A    |      | LINESTRING(7.7 3.8,7.7 5.8,9 5.8,7.7 3.8)
  2 | B    |    1 | MULTILINESTRING((0 0,4 0,4 4,0 4,0 0),(1 1,2 1,2 2,1 2,1 1))
  2 | B    |    2 | LINESTRING(-1 -1,-1 -2,-2 -2,-2 -1,-1 -1)
(3 rows)

LinesFromPolygon2, : p.geom, g, i.e.

p.geom := ST_Boundary(ST_GeometryN($1, m));