Postgresql trigger function not working

Question

I'm very new to trigger function. Actually this is the first time I'm using it, and I can't pass over an issue. Here is my code:

CREATE OR REPLACE FUNCTION altitude()
  RETURNS trigger AS $$
DECLARE
 maxaltimeter DOUBLE PRECISION;

BEGIN

  SELECT max(altitude) INTO maxaltimeter FROM waypoints WHERE flight_id = OLD.id;
  RETURN NEW;
  UPDATE flights SET max_altimeter = NEW.maxaltimeter WHERE id=OLD.id;


END;
$$
LANGUAGE plpgsql VOLATILE
COST 100;

CREATE TRIGGER set_altitude
  AFTER UPDATE OF status
  ON flights
  FOR EACH ROW
  EXECUTE PROCEDURE altitude();

When running UPDATE on the 'flights' table (status column) I don't get any results (but no errors too). Any ideas? Thank you.

user330315 · Accepted Answer · 2012-06-23 08:55:29Z

2

You got the statements in the wrong order. The RETURN NEW will terminate the trigger and thus the update will not be run.

It should be:

SELECT max(altitude) INTO maxaltimeter FROM waypoints WHERE flight_id = OLD.id;
UPDATE flights SET max_altimeter = NEW.maxaltimeter WHERE id=OLD.id;
RETURN NEW;

But I hope you are aware that this is not going to be a scalable solution.

Usually it's better to not store calculated/aggregated values in the database, but to calculate them while you retrieve the data. For convenience you might want to create a view that returns you the max() value for each flight.

The only reason why pre-calculating such a value would be if the retrieval is really expensive and you have to do it very often compared to the updates and the retrieval performance is more important than the update performance.

answered Jun 23, 2012 at 8:55

user330315

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

2 Comments

sica07 Over a year ago

thank you! It worked like a charm! Also, thanks for the suggestion regarding the view. I created a view with the max(altitude) and select from there the maxaltimeter.

wildplasser Over a year ago

Also take into account that the trigger can result in quadratic behaviour: if N records with the same flight_id are updated, the MAX() aggregate will be invoked N times, and each invocation needs to inspect (N+x) tuples. You could reduce the load a bit by bailing out early if new.altitude happens to be smaller than the current max_altimeter (which is most often the case)

Collectives™ on Stack Overflow

Postgresql trigger function not working

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related