时间戳

首页 > 解决方案 > Postgis 触发器：表和物化视图

sql - Postgis 触发器：表和物化视图

问题描述

我的“城市”有一个 postgis 多边形表。我的“boreholl”有一个点表。

我创建了一个由空间连接定义的“mv”物化视图，如下所示：

SELECT boreholl.*,
cities.gid,
cities.insee_id,
cities.name
FROM boreholl, cities
WHERE st_intersects(boreholl.geom, cities.geom);

现在，我希望一旦我的“城市”多边形表的“城市名称”列发生更新，这种更改也会发生在我的物化视图的“名称”列中。

据我了解，触发器必须在“城市”表上定义如下：

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

现在我需要定义函数。这一步我需要你。下面，一个建议的结构。但是缺少很多东西：

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

非常感谢。

标签： sqlpostgresqltriggerspostgismaterialized-views

解决方案

最简单的方法是在特定列上创建触发器，并且每次更新此列时mv都会刷新。：

CREATE OR REPLACE FUNCTION rebuild_materialized_view() 
RETURNS TRIGGER AS $$
BEGIN
  REFRESH MATERIALIZED VIEW mv;
  RETURN NEW;
END; $$ LANGUAGE plpgsql;

列特定触发器的正确​​语法...OF colum_name ON table_name...不仅是...ON table_name.... 此外，您可能还想在以下情况下刷新视图INSERT：

CREATE TRIGGER cities_trigger
AFTER INSERT OR UPDATE OF name ON cities
FOR EACH STATEMENT 
EXECUTE PROCEDURE rebuild_materialized_view();

这种方法的缺点是mv无论更新的记录和boreholl. 不过，根据您的桌子的大小，这应该不是问题。

演示：db<>fiddle

另一种（稍微复杂一点）的方法是使用 的值TG_OP。如果使用 调用触发器，INSERT并且如果新记录与 相交boreholl，则物化视图mv将被刷新。如果它是一个UPDATE并且更新的记录以前在mv并且name被更改过，mv也会被刷新：

CREATE OR REPLACE FUNCTION rebuild_materialized_view() 
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP ='INSERT' AND EXISTS (SELECT ST_Intersects(NEW.geom,b.geom) 
                                 FROM boreholl b) THEN
     REFRESH MATERIALIZED VIEW mv;
  ELSEIF TG_OP = 'UPDATE' AND 
       EXISTS (SELECT 1 FROM mv 
               WHERE gid = NEW.gid AND NEW.name <> OLD.name) THEN
      REFRESH MATERIALIZED VIEW mv;
  END IF;

  RETURN NEW;
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER cities_trigger
AFTER INSERT OR UPDATE ON cities
FOR EACH ROW EXECUTE PROCEDURE rebuild_materialized_view();

演示：db<>fiddle

推荐阅读