Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- Data set with 500 random points on 'cities' and a polygon (buffer) on 'boreholl'
CREATE TABLE boreholl AS
SELECT ST_Buffer('SRID=4326;POINT(-4.5153 54.1547)',1) AS geom;

CREATE TABLE cities (gid int, insee_id int, name text, geom geometry(point,4326));
INSERT INTO cities
SELECT j.path[1],j.path[1],'name '||j.path[1],j.geom 
FROM ST_Dump(ST_GeneratePoints(ST_Buffer('SRID=4326;POINT(-4.5153 54.1547)',1),500)) j;
INSERT INTO cities VALUES(1000,1000,'no interesction point','SRID=4326;POINT(1 1)');

CREATE MATERIALIZED VIEW mv AS
SELECT cities.gid,cities.insee_id,cities.name,cities.geom
FROM boreholl, cities 
WHERE st_intersects(boreholl.geom, cities.geom);
CREATE INDEX boreholl_geom_idx ON boreholl USING GIST (geom);
CREATE INDEX cities_geom_idx ON cities USING GIST (geom);
1 rows affected

500 rows affected

1 rows affected

500 rows affected

CREATE OR REPLACE FUNCTION rebuild_materialized_view() 
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP ='INSERT' AND EXISTS (SELECT ST_Intersects(NEW.geom,b.geom) 
                                 FROM boreholl b) THEN
     REFRESH MATERIALIZED VIEW mv;
  ELSEIF TG_OP = 'UPDATE' AND EXISTS (SELECT 1 FROM mv 
                                       WHERE gid = NEW.gid AND NEW.name <> OLD.name) THEN
      REFRESH MATERIALIZED VIEW mv;
  END IF;

  RETURN NEW;
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER cities_trigger
AFTER INSERT OR UPDATE ON cities
FOR EACH ROW EXECUTE PROCEDURE rebuild_materialized_view();
SELECT gid,insee_id,name,ST_AsEWKT(geom) FROM mv WHERE gid = 42;


gid
insee_id
name
st_asewkt


42
42
name 42
SRID=4326;POINT(-3.7148945941954 54.4269388504845)


UPDATE cities SET insee_id = insee_id+10;
-- no changes on 'mv', as the column 'name' wasn't triggered.
SELECT gid,insee_id,name,ST_AsEWKT(geom) FROM mv WHERE gid = 42;

UPDATE cities SET name = name||'updated' WHERE gid = 1000;
-- no changes on 'mv', as the record gid=1000 isn't in the view 'mv' (no intersection)
SELECT gid,insee_id,name,ST_AsEWKT(geom) FROM mv WHERE gid = 42;
501 rows affected



gid
insee_id
name
st_asewkt


42
42
name 42
SRID=4326;POINT(-3.7148945941954 54.4269388504845)


1 rows affected



gid
insee_id
name
st_asewkt


42
42
name 42
SRID=4326;POINT(-3.7148945941954 54.4269388504845)


UPDATE cities SET name = 'name-updated ' || gid WHERE gid = 42;
-- 'mv' is now refreshed, as the updated record has a changed name 
-- and was previously on 'mv'.
SELECT gid,insee_id,name,ST_AsEWKT(geom) FROM mv WHERE gid = 42;
1 rows affected



gid
insee_id
name
st_asewkt


42
52
name-updated 42
SRID=4326;POINT(-3.7148945941954 54.4269388504845)


INSERT INTO cities VALUES
(1001,1001,'new record that DOES NOT intersect','SRID=4326;POINT(1 2)');
-- 'mv' isn't refreshed, as the new record does not intesrsect with 'boreholl'
SELECT gid,insee_id,name,ST_AsEWKT(geom) FROM mv WHERE gid IN (42,1001);  

INSERT INTO cities VALUES
(1002,1002,'new record that intersects','SRID=4326;POINT(-3.93983208023945 53.8319960776811)');
-- 'mv' is now refreshed,as the new record does intersect.
SELECT gid,insee_id,name,ST_AsEWKT(geom) FROM mv WHERE gid IN (42,1001,1002);  
1 rows affected



gid
insee_id
name
st_asewkt


42
52
name-updated 42
SRID=4326;POINT(-3.7148945941954 54.4269388504845)


1 rows affected



gid
insee_id
name
st_asewkt


42
52
name-updated 42
SRID=4326;POINT(-3.7148945941954 54.4269388504845)

1002
1002
new record that intersects
SRID=4326;POINT(-3.93983208023945 53.8319960776811)
gid	insee_id	name	st_asewkt
42	52	name-updated 42	SRID=4326;POINT(-3.7148945941954 54.4269388504845)
1002	1002	new record that intersects	SRID=4326;POINT(-3.93983208023945 53.8319960776811)