Forum OpenACS Q&A: Response to triggers and aggregates PG 7.1

Its probably much easier to put the where clause into the procedure:

IF foo_x = foo_y
THEN
   do some some stuff;
   RETURN NULL;
ELSE
   RETURN NULL;
ENDIF;

As for the other problem, I think that you can cast the answer of the avg() to include more decimal places. Try looking up information about the numeric datatype. In the general-ratings module we wrote stored the average as a float. We also used a less expensive method of calculating the average (and std. devation!) by keeping track of some running totals. I'll enclose the code for fun:

create table cr_user_ratings (
       user_id		     integer not null references users,
       axis_id		     integer not null references cr_rating_axes,
       on_what_id	     integer not null,
       on_which_table        varchar(50,
       time_rated	     datetime not null,
       rating		     integer not null check (rating > 0 and rating <= 10),
       comment_id	     integer references general_comments, --id of associated general comment
       constraint unique_user_axis unique (user_id, axis_id, on_what_id)
);

create table cr_compiled_ratings (
       on_what_id		 integer not null,
       on_which_table		 varchar(50),
       axis_id			 integer not null references cr_rating_axes,
       average_rating		 float not null,
       last_compiled_on		 datetime not null,
       number_of_ratings	 integer not null,
       sum_ratings		 integer not null,
       sum_ratings_squared	 integer not null,
       std_dev_ratings		 float not null
);

-- equation for Standard Deviation comes from HP 11C owners handbook 
-- and problem-solving guide 😊

create function cr_maintain_compiled_ratings() returns opaque
as '
DECLARE
	-- the old compiled ratings information
	v_row cr_compiled_ratings%rowtype;	
BEGIN
   SELECT * into v_row
     FROM cr_compiled_ratings cr
    WHERE NEW.axis_id = cr.axis_id
      AND NEW.on_what_id = cr.on_what_id;

   IF FOUND 
   THEN
      UPDATE cr_compiled_ratings 
	 SET average_rating = (v_row.sum_ratings+NEW.rating)::float /
                (v_row.number_of_ratings+1)::float, 
             number_of_ratings = v_row.number_of_ratings + 1,
             sum_ratings = v_row.sum_ratings + NEW.rating,
             sum_ratings_squared = v_row.sum_ratings_squared + 
                (NEW.rating^2),
             std_dev_ratings = sqrt(
                  ( (v_row.number_of_ratings + 1) * 
                    (v_row.sum_ratings_squared + (NEW.rating^2) )
                    -
                    ( (v_row.sum_ratings + NEW.rating)^2 )
                  ) / 
                  ( v_row.number_of_ratings * (v_row.number_of_ratings + 1) ) 
                ),
	     last_compiled_on = sysdate()
         WHERE axis_id = NEW.axis_id
           AND on_what_id = NEW.on_what_id;
   ELSE
      -- create new entry in ratings table
      INSERT INTO cr_compiled_ratings
         (on_what_id, on_which_table, axis_id, 
	 average_rating, last_compiled_on, 
	 number_of_ratings, sum_ratings, 
	 sum_ratings_squared, std_dev_ratings)
      VALUES
         (NEW.on_what_id, NEW.on_which_table, NEW.axis_id,
	 NEW.rating, sysdate(),
         1, NEW.rating,
         NEW.rating^2, 0);
   END IF; 
   RETURN NULL;
END;' language 'plpgsql';

create trigger cr_maintain_compiled_ratings_tr 
after insert on cr_user_ratings
for each row
execute procedure cr_maintain_compiled_ratings();

The idea was to not do a table scan one updates of a rating.

Cheers,