pg polygon generation functions

gistfile1.sql

CREATE FUNCTION "public"."normalize_poly_to_bottomlevel"()
  RETURNS "pg_catalog"."varchar" AS $BODY$DECLARE
	i record;
	geo geometry;
	geos geometry[];
	geos_arr geometry[];
	boo boolean;
	levels bigint[];
	level_ bigint;
	temp record;
BEGIN
	for level_ in (SELECT DISTINCT level FROM mongo_regions ORDER BY level DESC) LOOP
		levels = levels || level_;
	END LOOP;
	FOREACH level_ IN ARRAY levels LOOP
		INSERT INTO log VALUES('level ' || level_::text,NOW());
		FOR i IN (SELECT * FROM mongo_regions mr WHERE mr.level = level_) LOOP
			IF (SELECT COUNT(*) FROM get_child_geo(i.m_id)) = 0 THEN
				UPDATE mongo_regions mr SET is_leaf = TRUE WHERE mr.m_id = i.m_id;
			END IF;
			SELECT ST_ConvexHull(ST_Collect(ARRAY(SELECT ST_MakeValid(get_child_geo(m.m_id)) FROM mongo_regions m WHERE m.id = i.id))) INTO geo;
			IF geo IS NOT NULL THEN
				INSERT INTO log VALUES(geo::text,NOW());
				UPDATE mongo_regions mr SET geometry = geo WHERE mr.id = i.id;
			END IF;
		END LOOP;
	END LOOP;

return 'OK';
END$BODY$
  LANGUAGE 'plpgsql' VOLATILE COST 100
;


CREATE FUNCTION "public"."get_child_geo"(m_id varchar)
  RETURNS SETOF "public"."geometry" AS $BODY$DECLARE
	temp record;
	i record;
BEGIN
	FOR i IN (SELECT * FROM mongo_regions WHERE parent_m_id = $1) LOOP
		RETURN NEXT i.geometry;
		RETURN QUERY (SELECT * FROM get_child_geo(i.m_id));
	
	END LOOP;
END$BODY$
  LANGUAGE 'plpgsql' VOLATILE COST 100
 ROWS 1000
;