2

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_function

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_function
returns trigger
langage sql
AS $$
BEGIN
 if    then
update
end if ;
return new
refresh materialized view
END;
$$
4
  • 1
    Trigger functions are supposed to be very quick events, since they're inside a commit. Trying to propagate a materialized view inside a trigger has got to be worst practice (if it's even possible).
    – Vince
    Commented Aug 10, 2021 at 20:21
  • 1
    Have you considered a view, rather than a marterialised view? Commented Aug 10, 2021 at 21:08
  • I considered the matérialised view rather than view because I read that the writing on a MV was faster. But considering your messages, as far as understood, the best way would be to store the join into a view. Then to set a trigger?
    – user35117
    Commented Aug 10, 2021 at 21:28
  • 1
    no need for a trigger if you use a view - just make sure there is a suitable index
    – Ian Turton
    Commented Aug 11, 2021 at 7:48

1 Answer 1

2

Creating a materialised view executes a query and writes the result to disk. It will only update the result set when told to do so (REFRESH MATERIALIZED VIEW {view table}).

A view will only execute the query when the view itself is queried (i.e. SELECT * FROM {view table} is essentially a proxy to the query defined by the CREATE VIEW command). A view is therefore not physically materialised (hence the name distinction).

So querying a non-materialised view will retrieve the latest information, there is no need for an insert trigger. Whether this is a better option for you than a materialised view depends on how long the query takes, and how often updates occur. I tend to use views a lot more frequently than materialised views, which I usually reserve for cases of heavy spatial operators.

If you still want to use a materialised view (there are good reasons this may be so), and you want the latest updates in your result set, then you must use REFRESH MATERIALISED VIEW before querying your view. Using an insert trigger is still an option, I think, but it probably isn't a good idea since triggers are meant to be quick record-level updates — I tend to use them for things like deriving geometries, e.g. so I can have a polygon and its centroid pre-calculated and available in the same table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.