Forum OpenACS Q&A: triggers and aggregates PG 7.1

Posted by David Kuczek on
Will it be possible for PG 7.1 to trigger with a "where-statement"? For example :

create trigger general_links_rating_update
after insert or update on general_link_user_ratings where foo_x = foo_y
for each row
execute procedure trig_general_links_rating_update();

And what about the avg(int) aggregate.
With general_links for example it rounds the integers that are inside general_links_user_ratings and updates avg_rating of general_links with a rounded integer and not with a more detailed numeric...

Any suggestions how you can get more detail into that avg_rating? I wanted to insert 10 instead of 1 and 100 instead of 10 into general_link_user_ratings and then do a tcl-job on the output...

Is that clever?
Posted by Carl Coryell-Martin on
Its probably much easier to put the where clause into the procedure:
IF foo_x = foo_y
   do some some stuff;

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 '
	-- the old compiled ratings information
	v_row cr_compiled_ratings%rowtype;	
   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;

      UPDATE cr_compiled_ratings 
	 SET average_rating = (v_row.sum_ratings+NEW.rating)::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 + 
             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;
      -- 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)
         (NEW.on_what_id, NEW.on_which_table, NEW.axis_id,
	 NEW.rating, sysdate(),
         1, NEW.rating,
         NEW.rating^2, 0);
   END IF; 
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.