[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.

[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...@gmail.com>
À: "PostGIS Users Discussion" <postgi...@lists.osgeo.org>
Envoyé: Mardi 10 Août 2021 21:44:57
Objet: [postgis-users] Postgis trigger : table and materialized view

[Fichier texte:ATT00001]