Trigger function does not update the first row of the table

Question

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

Laurenz Albe · Accepted Answer · 2016-07-29 14:25:46Z

1

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.

answered Jul 29, 2016 at 14:25

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lisdengard Over a year ago

Thanks Laurenz, good point on the incident AS i. I removed it and now the trigger works as expected even for the first row. Though I understand your point I am not sure why it was causing my problem, but the fact is, it works!

Collectives™ on Stack Overflow

Trigger function does not update the first row of the table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related