<html><body><div style="font-family: garamond,new york,times,serif; font-size: 12pt; color: #000000"><div>Hi Laurent,</div><div><br data-mce-bogus="1"></div><div>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).</div><div>Here is my trigger:</div><div><div>CREATE TRIGGER update_nbup</div><div> AFTER INSERT OR DELETE OR UPDATE </div><div> ON pasto.clim_definition_terr</div><div> FOR EACH STATEMENT</div><div> EXECUTE PROCEDURE pasto.update_nbup();</div><div><br data-mce-bogus="1"></div><div>And the function executed</div><div><div>CREATE FUNCTION pasto.update_nbup()</div><div> RETURNS trigger</div><div> LANGUAGE 'plpgsql'</div><div>AS $$</div><div>BEGIN</div><div><span style="white-space:pre"> </span>REFRESH MATERIALIZED VIEW pasto.clim_definition_territoire;</div><div><span style="white-space:pre"> </span>return null;</div><div>END;</div><div>$$;</div><div><br data-mce-bogus="1"></div><div>HTH</div><div>Hug</div></div></div><div><br></div><div data-marker="__SIG_PRE__"><div><p class="MsoNormal" style="margin:0cm 0cm 0.0001pt;font-size:11pt;font-family:'calibri' , sans-serif;background-color:#fdfcfa"><br></p><p class="MsoNormal" style="margin:0cm 0cm 0.0001pt;font-size:11pt;font-family:'calibri' , sans-serif;background-color:#fdfcfa"><br data-mce-bogus="1"></p></div></div><div><br></div><hr id="zwchr" data-marker="__DIVIDER__"><div data-marker="__HEADERS__"><b>De: </b>"celati Laurent" <laurent.celati@gmail.com><br><b>À: </b>"PostGIS Users Discussion" <postgis-users@lists.osgeo.org><br><b>Envoyé: </b>Mardi 10 Août 2021 21:44:57<br><b>Objet: </b>[postgis-users] Postgis trigger : table and materialized view<br></div><div><br></div><div data-marker="__QUOTED_TEXT__"><div dir="ltr"><div>Good evening,</div><br><div>I have a postgis polygons table for my "cities".<br>I have a point table for my "boreholl".<br><br>I created a "mv" materialized view defined by a spatial join as follows:<br><br><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb( 204 , 204 , 204 );padding-left:1ex"> SELECT boreholl.*,<br> cities.gid,<br> cities.insee_id,<br> <a href="http://cities.name" target="_blank" rel="nofollow noopener noreferrer">cities.name</a><br> FROM boreholl, cities<br> WHERE st_intersects(boreholl.geom, cities.geom);<br></blockquote><br>Now I want as soon as an updating takes place for the "cities_name" column of my "cities" polygon table,<br>this change also occurs in the "name" column of my materialized view.<br><br>As far i understood,the trigger must be defined on the "cities" table as follows :<br><br><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb( 204 , 204 , 204 );padding-left:1ex"> CREATE TRIGGER cities_trigger<br> after update<br> on cities<br> FOR EACH STATEMENT. <br> execute procedure trigger_fonction<br></blockquote><br><br>Now I need to define the function. It is for this step that I need you.<br>Below, a proposed structure. But a lot is missing :<br><br><br><blockquote> CREATE or replace FUNCTION trigger_fonction<br> returns trigger<br> langage sql<br> AS $$<br> BEGIN<br> if then<br> update<br> end if ;<br> return new<br> refresh materialized view<br> END;<br> $$<br></blockquote><br>Thanks a lot. </div></div>
<br><br>[Fichier texte:ATT00001]<br></div></div></body></html>