1

PostgreSQL 14

The goal of the function is to refresh a specific materialized view prescription - there are 8 mat views named schemaname.prescription - when the IF condition is true :

    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
DECLARE
    vm_prescription RECORD;
begin
    FOR vm_prescription in 
        select relname, relnamespace, nspname, relowner, relkind, nspowner from
        pg_catalog.pg_class join
        pg_catalog.pg_namespace
        on relnamespace = pg_catalog.pg_namespace.oid
        and relkind = 'm' and relname = 'prescription'
        order by nspname
    LOOP 
        IF (st_intersects(NEW.geom, *here is the problem*)) THEN
            EXECUTE format( 'refresh materialized view %I.%I', vm_prescription.nspname, vm_prescription.relname);
        END IF;
    END LOOP;
    return null;
END;
$BODY$;
  • I want to refresh the materialized view schemaname.prescription when the NEW.geom inserted (in the full table of course) intersects the geom of the schemaname.region.
  • I can't find the correct syntax for the second parameter of the st_intersects : I have to write something like (execute format('select geom from %I.region', vm_prescription.nspname)), which is obviously false but I hope the idea is clear.
    prescription is the name of all materialized views, only schema name changes.
Leehan
  • 145
  • 7
  • When you say "*the materialized view of a region*", how is a region's materialised view linked to the region's geometry? – Bergi Mar 10 '23 at 10:31
  • The first post is updated to be clearer. I made mistakes before. – Leehan Mar 10 '23 at 11:16

1 Answers1

2

I think you want something like this:

CREATE FUNCTION foo()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
DECLARE
   vm_prescription record;
   geom_found bool;
BEGIN
   FOR vm_prescription IN
      SELECT c.relname, c.relnamespace, n.nspname, c.relowner, c.relkind, n.nspowner
      FROM   pg_catalog.pg_class c
      JOIN   pg_catalog.pg_namespace n ON c.relnamespace = n.oid
      AND    c.relkind = 'm'
      AND    c.relname = 'prescription'
      ORDER  BY n.nspname
   LOOP
      EXECUTE format('SELECT EXISTS (SELECT FROM %I.region r WHERE ST_INTERSECTS(r.geom, $1))', vm_prescription.nspname)
      USING NEW.geom
      INTO geom_found;
      
      IF geom_found THEN
         EXECUTE format('REFRESH MATERIALIZED VIEW %I.%I', vm_prescription.nspname, vm_prescription.relname);
      END IF;
   END LOOP;
   RETURN NULL;
END
$func$;

NEW is not visible inside EXECUTE, so pass NEW.geom with the USING clause. See:

Another option would be to run a query with LIMIT 1, and then check with GET DIAGNOSTICS. See:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • I had an error because of `NEW` so I try `EXECUTE format('SELECT EXISTS (Select from %I.interregion r where st_intersects('|| NEW.geom ||', r.geom)', vm_prescription.nspname)` to make `NEW` existing for PL/pgSQL function. Now when inserting a feature, it says ; ` Erreur PostGIS lors de l'ajout d'entité : ERROR: operator is not unique: unknown || geometry LINE 1: ...Select from %I.region r where st_intersects('|| NEW.geo... ^ HINT: Could not choose a best candidate operator. You might need to add explicit type casts.` – Leehan Mar 10 '23 at 14:27
  • it seems I have to follow something like that : https://stackoverflow.com/questions/17575448/error-operator-is-not-unique-unknown-geometry-line-2-where-st-contains – Leehan Mar 10 '23 at 14:43
  • @Leehan: Yeah I missed a spot. Fixed now. – Erwin Brandstetter Mar 10 '23 at 16:00
  • That's it. It works on an insert. Thanks for the link as well. – Leehan Mar 10 '23 at 17:06