CODEWITHSUNDEEP
postgresqltriggersgis
Lisdengard
Lisdengard

Reputation: 139

Trigger function does not update the first row of the table

I am working in PostgreSQL 9.5 where I have these two tables:

Table incident:

  oid integer 
  description integer,
  geom geometry(Point,4326),

And table grid:

  oid integer,
  number integer,
  geom geometry(Polygon,4326)

Everytime a point is added to table incident I want the field description to be filled with the grid number that the point is intersecting. To achieve this I created this trigger:

CREATE OR REPLACE FUNCTION get_ortofoto_incidentes()
  RETURNS TRIGGER AS
$BODY$
   BEGIN      
       SELECT g.number INTO NEW.description 
       FROM incident AS i, grid AS g 
       WHERE st_intersects(new.geom, g.geom) ; -- 
RETURN NEW;
   END;
$BODY$
   LANGUAGE 'plpgsq;

And added it to the table incident:

CREATE TRIGGER get_ortofoto_incidentes
  BEFORE INSERT OR UPDATE ON incidentestest  
  FOR each row
  EXECUTE PROCEDURE get_ortofoto_incidentes();

The problem is that when the trigger function is called the attribute description remains null for the first row, but after the trigger works as expected. This is what I am getting:

oid | description
 1  |  null
 2  | 236541
 3  | 695489
 4  | 325687
... |  ....

Any idea why the trigger is not updating for the first row of the table?

Thank you

Upvotes: 0

Views: 97

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246493

I'd say that it is a coincidence that this happens only for the first inserted row. It is completely ok for description to be set to NULL if the first matching row in grid has NULL in number.

I also want to warn you that the query in your trigger function builds a Cartesian product between the matching rows from grid and the table incident, since there is no WHERE condition restricting the latter. Remove the incident AS i from the query.

Upvotes: 1

Related Questions