Ticket #76: my_st_dump_points.sql

CREATE FUNCTION _my_ST_DumpPoints(the_geom geometry, cur_path integer[]) RETURNS SETOF geometry_dump AS $$
DECLARE
  tmp geometry_dump;
  nb_points integer;
  nb_geom integer;
BEGIN

  -- Special case (POLYGON) : return the points of the exterior ring of a polygon
  IF (ST_GeometryType(the_geom) = 'ST_Polygon') THEN

    -- Support PostgreSQL < 8.4
    FOR tmp IN SELECT * FROM _my_ST_DumpPoints(ST_ExteriorRing(the_geom), cur_path) LOOP
      RETURN NEXT tmp;
    END LOOP;

    -- PostgreSQL 8.4 required
    -- RETURN QUERY (SELECT * FROM _my_ST_DumpPoints(ST_ExteriorRing(the_geom), NULL));

    RETURN;
  END IF;

  -- Special case (MULTI* OR GEOMETRYCOLLECTION) : iterate and return the DumpPoints of the geometries
  SELECT ST_NumGeometries(the_geom) INTO nb_geom;

  IF (nb_geom IS NOT NULL) THEN
    
    FOR i IN 1..nb_geom LOOP

      -- Support PostgreSQL < 8.4
      FOR tmp IN SELECT * FROM _my_ST_DumpPoints(ST_GeometryN(the_geom, i), cur_path || ARRAY[i]) LOOP
        RETURN NEXT tmp;
      END LOOP;

      -- PostgreSQL 8.4 required
      -- RETURN QUERY (SELECT * FROM _my_ST_DumpPoints(ST_GeometryN(the_geom, i), cur_path || ARRAY(i)));
    END LOOP;

    RETURN;
  END IF;

  -- Special case (POINT) : return the point
  IF (ST_GeometryType(the_geom) = 'ST_Point') THEN

    tmp.path = cur_path || ARRAY[1];
    tmp.geom = the_geom;

    RETURN NEXT tmp;
    RETURN;

  END IF;

  -- Use ST_NumPoints rather than ST_NPoints to have a NULL value if the_geom isn't
  -- a LINESTRING or CIRCULARSTRING.
  SELECT ST_NumPoints(the_geom) INTO nb_points;

  -- This should never happen
  IF (nb_points IS NULL) THEN
    RAISE EXCEPTION 'Unexpected error while dumping geometry %', ST_AsText(the_geom);
  END IF;

  FOR i IN 1..nb_points LOOP
    tmp.path = cur_path || i;
    tmp.geom := ST_PointN(the_geom, i);
    RETURN NEXT tmp;
  END LOOP;
END
$$ LANGUAGE plpgsql;

CREATE FUNCTION my_ST_DumpPoints(geometry) RETURNS SETOF geometry_dump AS $$
  SELECT * FROM _my_ST_DumpPoints($1, NULL);
$$ LANGUAGE SQL;