Postgis trigger : table and materialized view

Question

I have a postgis polygons table for my "cities". I have a point table for my "boreholl".

I created a "mv" materialized view defined by a spatial join as follows:

SELECT boreholl.*,
cities.gid,
cities.insee_id,
cities.name
FROM boreholl, cities
WHERE st_intersects(boreholl.geom, cities.geom);

Now I want as soon as an updating takes place for the "cities_name" column of my "cities" polygon table, this change also occurs in the "name" column of my materialized view.

As far i understood,the trigger must be defined on the "cities" table as follows :

CREATE TRIGGER cities_trigger
after  update
on cities
FOR EACH STATEMENT. 
execute procedure trigger_fonction

Now I need to define the function. It is for this step that I need you. Below, a proposed structure. But a lot is missing :

CREATE or replace FUNCTION  trigger_fonction
returns trigger
langage sql
AS $$
BEGIN
 if    then
update
end if ;
return new
refresh materialized view
END;
$$

Thanks a lot.

user_2275388 · Answer

The easiest approach is to create a trigger on a specific column and every time this column gets updated mv will be refreshed.:

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

The correct syntax of column specific triggers is ...OF colum_name ON table_name..., not only ...ON table_name.... Also, you probably want to refresh the view also in case of an INSERT:

CREATE TRIGGER cities_trigger
AFTER INSERT OR UPDATE OF name ON cities
FOR EACH STATEMENT 
EXECUTE PROCEDURE rebuild_materialized_view();

The downside of this approach is that mv will be refreshed regardless of intersection between the updated record and boreholl. Depending on the size of your tables this shouldn't be an issue though.

Demo: db<>fiddle

A further (slightly more complex) approach would be to use the values of TG_OP. If the trigger is being called with an INSERT and if the new record intersects with boreholl, the materialized view mv gets refreshed. If it is an UPDATE and the updated record was previously in mv and name was changed, mv also gets refreshed:

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();

Demo: db<>fiddle

Postgis trigger : table and materialized view

1 Answers