BUG #5042: Update numeric within a rule - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      5042
Logged by:          Ilian Georgiev
Email address:      georgiev.ilian@gmail.com
PostgreSQL version: 8.1.10
Operating system:   Windows XP
Description:        Update numeric within a rule
Details:

Hello I have a sutuation where I can do update on numeric column with a
signle update but when this update statement is in a rule it doesn't wotk
properly.
Look :

I have a table with videos :

CREATE TABLE video (
  video_sid SERIAL PRIMARY KEY,
  category_sid int NOT NULL REFERENCES category ON UPDATE RESTRICT ON DELETE
RESTRICT,
  url varchar(32) NOT NULL,
  user_sid int NOT NULL REFERENCES "user" ON UPDATE RESTRICT ON DELETE
RESTRICT,
    image_sid int REFERENCES image,
    creation_datetime timestamp NOT NULL DEFAULT NOW(),
    view_count int NOT NULL DEFAULT 0,
    comment_count int NOT NULL DEFAULT 0,
    rating numeric(4,2) NOT NULL DEFAULT 0,
    rating_percent int NOT NULL DEFAULT 0,
    votes int NOT NULL DEFAULT 0,
    is_published boolean NOT NULL DEFAULT false,
    title varchar(128) NOT NULL,
    description text
);

GRANT INSERT, UPDATE, SELECT ON video TO web;
GRANT SELECT, UPDATE ON video_video_sid_seq TO web;

COMMENT ON TABLE video IS 'Holds video desctiptions';

CREATE OR REPLACE FUNCTION update_rating_percent()
  RETURNS "trigger" AS
$BODY$
    BEGIN

        IF (NEW.rating!=0) THEN
            NEW.rating_percent := ((NEW.rating / 5 ) * 100)::int;
        END IF;

        RETURN NEW;
    END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

GRANT EXECUTE ON FUNCTION update_rating_percent() TO web;

CREATE TRIGGER update_rating_percent_trg
  BEFORE UPDATE
  ON video
  FOR EACH ROW
  EXECUTE PROCEDURE update_rating_percent();

and table with votes :

CREATE TABLE video_vote (
  video_sid int NOT NULL REFERENCES video ON UPDATE CASCADE ON DELETE
CASCADE,
    ip_address inet NOT NULL,
    rate int NOT NULL CHECK (rate > 0 AND rate < 6),
    creation_datetime timestamp NOT NULL DEFAULT NOW()
);

GRANT INSERT, UPDATE, DELETE, SELECT ON video_vote TO web;

COMMENT ON TABLE video_vote IS 'Votes of every user by IP address';

CREATE UNIQUE INDEX video_vote_ukey ON video_vote (video_sid, ip_address);

with a rule connected to the video table :

CREATE OR REPLACE RULE
    video_vote_insert_rule
AS ON INSERT TO
    video_vote
DO ALSO
    UPDATE
        video
    SET
        votes = votes + 1,
        rating = (( SELECT
                                    SUM(rate)::numeric
                             FROM
                                    video_vote
                             WHERE
                                    video_sid = NEW.video_sid
                            ) / (votes + 1) )::numeric
    WHERE
        video_sid = NEW.video_sid;

now when I do simple update on video it gets the right value for scale.But
when I do insert on video_vote and this do update on video table I got .00
for scale.

I even changed my rule to :

CREATE OR REPLACE RULE
    video_vote_insert_rule
AS ON INSERT TO
    video_vote
DO ALSO
    UPDATE
        video
    SET

        rating = 2.95
    WHERE
        video_sid = NEW.video_sid;

The result in rating column was 2.00 .
When I do :

update
  video
set
  rating = 2.95
where
  video_sid = 1;

Then the result is expected = 2.95