The following bug has been logged online:
Bug reference: 5042
Logged by: Ilian Georgiev
Email address: georgiev.il...@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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
