up vote 0 down vote favorite

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.

share|improve this question
96% accept rate
feedback

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
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. – sica07 Jun 23 '12 at 9:43
1  
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) – wildplasser Jun 23 '12 at 14:46
feedback

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.