explode a feature collection into a table

featCollection.sql

/**
*   return: a table of all valid polygons found in the featue collection along with their properties
*   error: 
*      - will not stop execution geometry will be set to empty (should be null?)
*      - for all indexes with an error an object with the key `error` and an array 
*         of deatails will be appended
*      ```
*          {"error": [ {"reason":'cross over', "locaiton":'<WKT>'}
*                  ,{"reason:": "invalid type"}]}
*      ```
*/
CREATE OR REPLACE FUNCTION import_featColl(
  featColPoly jsonb
)
RETURNS TABLE(
  idx int,
  type varchar,
  properties jsonb,
  geom geometry(Multipolygon,4326)
)
LANGUAGE plpgsql AS
$BODY$
begin
  -- extract geometry from feature collection
  CREATE TEMP TABLE user_features
    ON COMMIT DROP AS
  SELECT
    f.id::int as idx,
    (feat #>> ARRAY['geometry','type'])::varchar as type,
    feat -> 'properties' as properties,
    ST_SRID(ST_GeomFromGeoJSON(feat->>'geometry' )) as srid,
    CASE WHEN (feat->'geometry') ?& ARRAY['crs']
      THEN ST_Transform(ST_GeomFromGeoJSON(feat->>'geometry'),4326)
      ELSE ST_SetSRID(ST_GeomFromGeoJSON(feat->>'geometry'),4326)
    END as geom
  FROM (
    SELECT t.*
    FROM jsonb_array_elements(featColPoly->'features')
      WITH ORDINALITY AS t(feat,id)
  ) as f;

  -- table to hold validtion errors
    CREATE TEMP TABLE error
    (
      idx int not null,
      msg jsonb not null
    )
    ON COMMIT DROP;

    -- make a list of all non-polygon features
    INSERT INTO
      error(idx, msg)
    SELECT
      user_features.idx,
      format('{"message":"Feature type(%s) must be Polygon or Multipolygon."}', user_features.type)::jsonb
    FROM user_features
    WHERE
      user_features.type NOT ILIKE '%polygon';

    -- make a list of all flaws in all the geoms
    INSERT INTO
      error(idx, msg)
    SELECT
      user_features.idx, to_jsonb(ST_IsValidDetail(user_features.geom))
    FROM user_features
    WHERE
      NOT ST_IsValid(user_features.geom);

    RETURN QUERY
      SELECT
        s.idx,
        s.type,
        s.properties || to_jsonb(t),
        CASE
          WHEN t."error" IS NOT NULL THEN
            'SRID=4326;MULTIPOLYGON EMPTY'::geometry(Multipolygon,4326)
          ELSE
            s.geom
        END AS geom
      FROM user_features as s
      CROSS JOIN LATERAL (
         SELECT
          (SELECT jsonb_agg(msg) FROM error
          WHERE s.idx = error.idx )as error
       ) as t("error");
END;
$BODY$;

  LANGUAGE plpgsql IMMUTABLE SECURITY DEFINER
  COST 100;

-- vim: expandtab tabstop=2 softtabstop=2 shiftwidth=2
-- vim: ft=postgresql

improved error handling