sql - iterate and write separately linestrings (generate_series) -

i have table full of mix of polygons , multipolygons , run single function on break them linestrings (or multilinestrings).

my problem - returning set ... no geometry (?)...

the function looks this...(final based on mike t. help)

create or replace function linesfrompoly2(polygon geometry)  returns setof geometry_dump  $body$declare   m integer;   g geometry;  p geometry_dump%rowtype;  begin     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)   public.poly_and_multipoly; 

returns:

1|a|({1},) 2|b|({1},) 2|b|({2},) 

sample data:

create table poly_and_multipoly (   "id" serial not null primary key,   "name" char(1) not null,   "the_geom" geometry not null ); -- add data, polygon, b multipolygon insert 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 ); 

you don't need custom function want. instance, try accessing 2 members of st_dump (path , geom):

select id, name,   (st_dump(st_boundary(the_geom))).path[1],   st_astext((st_dump(st_boundary(the_geom))).geom) poly_and_multipoly;   id | name | path |                 st_astext ----+------+------+-------------------------------------------   1 |    |      | 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 1 [multi]linestring per geometry part:

select id, name,   (st_dump(the_geom)).path[1],   st_astext(st_boundary((st_dump(the_geom)).geom)) poly_and_multipoly;   id | name | path |                          st_astext ----+------+------+--------------------------------------------------------------   1 |    |      | 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) 

but if did want use linesfrompolygon2 function, fix simple: assign geometry p.geom, not g, i.e.

p.geom := st_boundary(st_geometryn($1, m));