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

[celati Laurent]

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,
>     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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20210810/64e41597/attachment.html>