[postgis-users] Postgis trigger : table and materialized view

[Hugues François]

Hi Laurent, 

I'm may not understand your concern but I think I do the same kind of thing and it seems quite simple (since the materialized view do the job, there's no need to use any special trigger parameters). 
Here is my trigger: 
CREATE TRIGGER update_nbup 
AFTER INSERT OR DELETE OR UPDATE 
ON pasto.clim_definition_terr 
FOR EACH STATEMENT 
EXECUTE PROCEDURE pasto.update_nbup(); 

And the function executed 
CREATE FUNCTION pasto.update_nbup() 
RETURNS trigger 
LANGUAGE 'plpgsql' 
AS $$ 
BEGIN 
REFRESH MATERIALIZED VIEW pasto.clim_definition_territoire; 
return null; 
END; 
$$; 

HTH 
Hug 









De: "celati Laurent" <laurent.celati at gmail.com> 
À: "PostGIS Users Discussion" <postgis-users at lists.osgeo.org> 
Envoyé: Mardi 10 Août 2021 21:44:57 
Objet: [postgis-users] Postgis trigger : table and materialized view 

Good evening, 

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, 
[ http://cities.name/ | 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 : 


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

BQ_END


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 : 



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

BQ_END

Thanks a lot. 


[Fichier texte:ATT00001] 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20210811/4b4233d9/attachment.html>