Queries with functions that return many rows

Question

I've a table with a geometry column. Those geometries are often multipolygons or multilinestrings and I would like to split them into their constituent parts without duplicating the other data in there rows. At the moment i use the following:

CREATE TABLE newTable AS SELECT index, ST_Dump(geometry) AS geometry FROM initialTable

This works without a problem and produces newTable with polygons and linestrings and a reference column for it's row in the initialTable containing other relevent info.

I would however like to incorporate progress tracking found here as the above statement can take some time.

I would therefore like to split the above statement into 1) creating the table 2) populating the table

The first is easy, but populating the table escapes me as one multilinestring row can create many linestrings rows.

Any assistance would be greatly appreciated.

Answer 1

Use ST_Dump in a SELECT and redirect the result set to the INSERT statement, e.g.:

CREATE TABLE newTable (gid int, geom geometry(linestring,4326));

INSERT INTO newtable 
SELECT (ST_Dump(geom)).path[1],(ST_Dump(geom)).geom 
FROM initialTable;