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;

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);
1 rows affected

500 rows affected

500 rows affected

CREATE OR REPLACE FUNCTION rebuild_materialized_view() 
RETURNS TRIGGER AS $$
BEGIN
  REFRESH MATERIALIZED VIEW mv;
  RETURN NEW;
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER cities_trigger
AFTER INSERT OR UPDATE OF name ON cities
FOR EACH STATEMENT EXECUTE PROCEDURE rebuild_materialized_view();
SELECT * FROM mv WHERE gid = 42;


gid
insee_id
name
geom


42
42
name 42
0101000020E6100000730947AF624415C0D5743C9887F94A40


UPDATE cities SET insee_id = insee_id+10;
-- no changes on 'mv', as the column 'name' wasn't triggered.
SELECT * FROM mv WHERE gid = 42; 
500 rows affected



gid
insee_id
name
geom


42
42
name 42
0101000020E6100000730947AF624415C0D5743C9887F94A40


UPDATE cities SET name = 'name-updated ' || gid WHERE gid = 42;
-- 'mv' is now refreshed.
SELECT * FROM mv WHERE gid = 42; 
1 rows affected



gid
insee_id
name
geom


42
52
name-updated 42
0101000020E6100000730947AF624415C0D5743C9887F94A40