Início / dba / Perguntas / 266409
Accepted
s.k
Asked: 2020-05-04 09:52:42 +0800 CST

O gatilho PostGIS é acionado sem erro, mas a ação parece não ser executada

  • 772

Contexto

Eu configurei um gatilho em uma tabela PostgreSQL (+ Postgis ) contendo pontos de observação.

Este acionador deve calcular uma nova geometria para cada ponto inserido ou atualizado. Esta nova geometria deve ser calculada como a projeção da geometria do ponto de recurso inserido atualmente na linha mais próxima de outra tabela contendo recursos de linha.

O que eu fiz

O gatilho é o seguinte, ele é disparado corretamente conforme EXPLAIN ANALYZEmostra após uma nova inserção:

EXPLICAR ANALISAR resultados

nenhum erro é lançado quando insiro um novo ponto, mas a coluna de geometria projetada proj_geompermanece vazia:

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();

Como descobri os gatilhos ontem à noite, e mesmo que tenha feito o meu melhor, meu código provavelmente está cheio de erros.
Eu também notei alguns scripts SQL que eu já tenho e que estão realmente funcionando, ... não funciona mais nesta maneira extravagante(?) de escrever gatilhos (por exemplo, eu não sei o significado do cifrão duplo $$).

Aviso :
Se eu retirar a SELECTdeclaração mais interna e substituir a palavra NEWpelo nome real da tabela de pontos, a consulta, quando executada diretamente no pgAdmin , com um ID especificado, como WHERE points.id = 41retorna os resultados corretos:

Resultados

Na trigger, meu primeiro comentário diz respeito ao fato de desejar poder atualizar ao mesmo tempo os campos line_id, diste proj_geomda tabela pointscada vez que uma observação for inserida.

Pergunta

Como eu poderia consertar meu código, pelo menos para conseguir a nova geometria?
Tenho certeza de que estou perdendo alguns pequenos detalhes (provavelmente onde deixei alguns comentários no código onde tenho essa sensação real), mas não consigo entendê-los e fiz muito hit'n'try agora com a função de gatilho (nenhum deles funcionou tão bem quanto o aqui acima).

Meio Ambiente

Ubuntu 18.04
PostgreSQL: 10.12
PostGIS: 2.4

postgresql trigger

1 respostas

  1. Best Answer

    Em um gatilho BEFORE INSERT, a nova linha ainda não foi inserida na tabela. Portanto, quando a consulta procurar points.idcom o novo valor, ela não o encontrará.

    E não há necessidade de ler a pointstabela porque você deseja acessar apenas a nova linha, e todos esses valores estão disponíveis em 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

    Você precisa usar um gatilho BEFORE para poder modificar a linha antes que ela seja gravada na tabela.

    Para uma pesquisa de vizinho mais próximo, é melhor usar o operador <-> , que é capaz de usar um índice geométrico em lines.geom.

    • 2

relate perguntas