-- Function: public.insert_equal_geoms(text, text, text, text)

-- DROP FUNCTION public.insert_equal_geoms(text, text, text, text);

CREATE OR REPLACE FUNCTION public.insert_equal_geoms(
    param_1 text, -- tindex
    param_2 text, --complete topo50
    param_3 text, -- error table
    param_4 text) -- spatially equal table
  RETURNS void AS
$BODY$
DECLARE
   v_row_count integer;
BEGIN

v_row_count := NULL;

   ---make error table if there are any instances of geometries not being equal to topo50 1k layer
EXECUTE format('
   DROP TABLE IF EXISTS public.%s;
   CREATE TABLE public.%s (
   geom geometry(MultiPolygon, 2193),indexname varchar(100),predicate varchar(250));

   INSERT INTO public.%s
   SELECT a.geom, b.indexname
   FROM public.%s as a
   LEFT JOIN public.%s as b ON ST_Equals(a.geom, b.geom)
   WHERE b.indexname IS NULL;',param_3,param_3,param_3,param_1,param_2);

GET DIAGNOSTICS v_row_count = ROW_COUNT;

IF v_row_count = 0 THEN

    EXECUTE format('DROP TABLE IF EXISTS public.%s;
       CREATE TABLE public.%s (
       geom geometry(MultiPolygon, 2193),indexname varchar(100),predicate varchar(250));

       INSERT INTO public.%s
       SELECT b.geom, b.tilename, ''Spatially Equal''
       FROM public.%s as a, public.%s as b
       WHERE ST_Equals(a.geom, b.geom);',param_4, param_4, param_4, param_1, param_2);

    RAISE NOTICE '%: Table updated.', timeofday()::timestamp;
ELSE
    RAISE NOTICE '%: % errors found. Check error table and resolve issues.', timeofday()::timestamp, v_row_count;
END IF;

END;
   $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.insert_equal_geoms(text, text, text, text)
  OWNER TO postgres;

SELECT public.insert_equal_geoms(public.sample1,public.sample2,public.sample3,public.sample4)