Yup, I’m going for hits with the above title.  So, I’ve been playing with triggers in PostGIS (PostgreSQL) trying to streamline a process that I go through quarterly.  We have a geographic boundary layer for our public entity:

The green area is the area with public access.  The purple without.  I want to maintain a version of the boundary with this very simple distinction, and another version of it in which the boundary is dissolved.  Triggers seem ideal here.  When I update the first, I get a version of the second (see below)

The command to do this is as follows:

CREATE TABLE public.reservation_boundaries_public_private_cm_dissolved
 AS
 SELECT res, ST_Union(the_geom) AS the_geom
  FROM public.reservation_boundaries_public_private_cm
   GROUP BY res;

So far, so good (sorry about the awful table names).  Now it needs to run each time there is an insert on the original table.  Here is what I came up with:

DROP TRIGGER dissolver ON public.reservation_boundaries_public_private_cm;

CREATE OR REPLACE FUNCTION dissolve_bounds() RETURNS TRIGGER AS $dissolver$

BEGIN
 IF(TG_OP='INSERT') THEN

 DROP TABLE IF EXISTS public.reservation_boundaries_public_private_cm_dissolved;

 CREATE TABLE public.reservation_boundaries_public_private_cm_dissolved
 AS
 SELECT res, ST_Union(the_geom)
   AS the_geom
   FROM public.reservation_boundaries_public_private_cm
   GROUP BY res;

 END IF;
 RETURN NEW;
END;

$dissolver$ LANGUAGE plpgsql;

CREATE TRIGGER dissolver
  AFTER INSERT ON public.reservation_boundaries_public_private_cm
  FOR EACH STATEMENT EXECUTE PROCEDURE dissolve_bounds();

My only complaint here is speed– it takes about 45 seconds (instead of close to 0.3 seconds) now to insert all my records.  I hoped that I could speed it up with the addition of

FOR EACH STATEMENT EXECUTE PROCEDURE

instead of

FOR EACH RECORD EXECUTE PROCEDURE

but the difference in speed is undetectable.  Any suggestions on how to speed this puppy up?