From: | "Ilian Georgiev" <georgiev(dot)ilian(at)gmail(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #5042: Update numeric within a rule |
Date: | 2009-09-06 20:48:31 |
Message-ID: | 200909062048.n86KmVfw007563@wwwmaster.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged online:
Bug reference: 5042
Logged by: Ilian Georgiev
Email address: georgiev(dot)ilian(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tomasz Karlik | 2009-09-07 06:28:45 | Odp: Re: BUG #5035: cast 'text' to 'name' doesnt work in plpgsqlfunction |
Previous Message | Martin Pitt | 2009-09-06 20:46:58 | Re: BUG #5041: Changing data_directory problem |