主页 / dba / 问题 / 266409
Accepted
s.k
Asked: 2020-05-04 09:52:42 +0800 CST

PostGIS 触发器触发没有错误，但该操作似乎未执行

  • 772

语境

我在包含观察点的PostgreSQL (+ Postgis ) 表上设置了一个触发器。

此触发器必须为每个插入或更新的点计算一个新几何。这个新几何必须计算为当前插入的特征点的几何投影到另一个包含线特征的表的最近线上。

我做了什么

触发器如下，EXPLAIN ANALYZE在新插入后正确触发：

解释分析结果

插入新点时不会引发错误，但投影几何列proj_geom保持为空：

CREATE OR REPLACE FUNCTION project_funct()
  RETURNS trigger AS
  $$
  BEGIN
    -- Here I wish I could update several fields (line_id, dist and proj_geom) at the same time
    -- but for the moment I only put the focus on the new geometry proj_geom:
    NEW.projected_geom := ( 
      SELECT sub.proj_geom
      FROM
        ( SELECT
             points.id AS point_id,
             schema.lines.id AS line_id,
             ST_Distance(schema.lines.geom, NEW.geom) AS dist,
             ST_ClosestPoint(schema.lines.geom, NEW.geom) AS proj_geom
          FROM schema.line, points
            -- Something is weird to me there, I should probably not have this WHERE statement here as the 
            -- trigger should be used on a single row at a time, hence with a single ID for each execution:
            WHERE points.id = NEW.id 
            ORDER BY dist
            LIMIT 1
        ) AS sub
    );
      RETURN NEW;
  END
  $$
LANGUAGE 'plpgsql';
CREATE TRIGGER proj_trigger
    -- Here, I don't really know if I must used BEFORE or AFTER, AFTER sounds better to me 
    -- but I may be totally wrong. I also wish I could keep the same code for both UPDATE and
    -- INSERT but this may not be a good idea?:
    BEFORE INSERT OR UPDATE ON points 
    FOR EACH ROW
    EXECUTE PROCEDURE project_funct();

正如我昨天晚上发现的触发器一样，即使我已经尽力了，我的代码也可能充满了错误。
我还注意到一些我已经拥有并且实际上正在工作的 SQL 脚本，......不再以这种花哨的（？）编写触发器的方式工作（例如，我不知道双美元符号的含义$$）。

注意：
如果我取出最里面的SELECT语句，并将单词替换为NEWpoints 表的实际名称，则查询，当直接在具有指定 ID 的pgAdmin中运行时，例如WHERE points.id = 41返回正确的结果：

结果

在触发器中，我的第一条评论与我希望能够在每次插入观察时同时更新表的line_id,dist和proj_geom字段这一事实有关。points

问题

我怎样才能修复我的代码，至少能够获得新的几何图形？
我很确定我遗漏了一些微小的细节（可能是我在代码中留下了一些我有实际感觉的注释的地方）但我无法弄清楚它们，我现在已经做了太多的hit'n'try使用触发功能（它们都不像上面的那样好用）。

环境

Ubuntu 18.04
PostgreSQL：10.12
PostGIS：2.4

postgresql trigger

1 个回答

  1. Best Answer

    在 BEFORE INSERT 触发器中，新行尚未插入到表中。因此，当查询points.id使用新值搜索时，它不会找到它。

    并且不需要读取points表格，因为您只想访问新行，并且所有这些值都可以在NEW：

          SELECT
         NEW.id AS point_id,
         schema.lines.id AS line_id,
         schema.lines.geom <-> NEW.geom AS dist,
         ST_ClosestPoint(schema.lines.geom, NEW.geom) AS proj_geom
      FROM schema.line
      ORDER BY dist
      LIMIT 1

    您需要使用 BEFORE 触发器才能在将行写入表之前对其进行修改。

    对于最近邻搜索，最好使用<-> 运算符，它能够在lines.geom.

    • 2

相关问题