From: | Daniel Schuchardt <daniel_schuchardt(at)web(dot)de> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | problem with rules - column values lost |
Date: | 2005-03-22 08:52:04 |
Message-ID: | d1ombi$58g$1@news.hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi List,
there seem to be a bug in the 8.0 Rule System if I update a view and
does not give a column an value.
example
TEST=# \d abzu_ruletest
View "public.abzu_ruletest"
Column | Type | Modifiers
------------+-------------------+-----------
abz_txt | character varying |
abz_id | integer |
abz_proz | real |
abz_betrag | real |
View definition:
SELECT lang_abzu(abzu.abz_id) AS abz_txt, abzu.abz_id, abzu.abz_proz,
abzu.abz_betrag FROM abzu;
TEST=# SELECT * FROM abzu_ruletest;
abz_txt | abz_id | abz_proz | abz_betrag
---------+--------+----------+------------
| 9 | 6 | 3
(1 row)
TEST=# UPDATE "abzu_ruletest" SET "abz_betrag"=3, abz_txt='test' WHERE
"abz_id"=9;
UPDATE 1
TEST=# SELECT * FROM abzu_ruletest;
abz_txt | abz_id | abz_proz | abz_betrag
---------+--------+----------+------------
test | 9 | 6 | 3
(1 row)
TEST=# UPDATE "abzu_ruletest" SET "abz_betrag"=3 WHERE "abz_id"=9;
UPDATE 1
TEST=# SELECT * FROM abzu_ruletest;
abz_txt | abz_id | abz_proz | abz_betrag
---------+--------+----------+------------
| 9 | 6 | 3
As you can see the Value of abz_txt is lost. The reason seems to be the
on Update rule, i fully delete the old record of the child table and
insert a new record there:
(i do not know if a record exists)
RULE :
UPDATE abzu SET abz_id=new.abz_id, abz_proz=new.abz_proz,
abz_betrag=new.abz_betrag WHERE abz_id=old.abz_id; ------------------OK
DELETE FROM abzutxt WHERE abzl_abz_id=old.abz_id; <----------------HERE
INSERT INTO abzutxt (abzl_abz_id, abzl_txt) VALUES (new.abz_id,
new.abz_txt);
=============
Definitions / Testcase
CREATE TABLE abzu
(abz_id SERIAL PRIMARY KEY,
abz_proz FLOAT4,
abz_betrag FLOAT4
);
CREATE TABLE abzutxt
(abzl_id SERIAL NOT NULL PRIMARY KEY,
abzl_abz_id INTEGER NOT NULL REFERENCES abzu ON DELETE CASCADE,
/*LANGUAGE CODE VARCHAR*/
abzl_txt VARCHAR(50)
);
CREATE OR REPLACE FUNCTION lang_abzu(INTEGER /*, VARACHAR (LANGUAGE
CODE)*/) RETURNS VARCHAR AS'
BEGIN
RETURN abzl_txt FROM abzutxt WHERE abzl_abz_id=$1 /* AND LANUAGE CODE
= CURRENT_USER_SETTING*/;
END'LANGUAGE plpgsql;
/*Normally everywhere actual Language codes*/
CREATE OR REPLACE VIEW abzu_ruletest AS
SELECT lang_abzu(abz_id) AS abz_txt, * FROM abzu;
CREATE RULE abzu_lang_insert AS ON INSERT TO abzu_ruletest DO INSTEAD
(INSERT INTO abzu (abz_id, abz_proz, abz_betrag) VALUES (new.abz_id,
new.abz_proz, new.abz_betrag);
INSERT INTO abzutxt (abzl_abz_id, abzl_txt) VALUES (new.abz_id,
new.abz_txt);
);
CREATE RULE abzu_lang_update AS ON UPDATE TO abzu_ruletest DO INSTEAD
(UPDATE abzu SET abz_id=new.abz_id, abz_proz=new.abz_proz,
abz_betrag=new.abz_betrag WHERE abz_id=old.abz_id;
DELETE FROM abzutxt WHERE abzl_abz_id=old.abz_id;
INSERT INTO abzutxt (abzl_abz_id, abzl_txt) VALUES (new.abz_id,
new.abz_txt);
);
INSERT INTO "abzu_ruletest" ("abz_id", "abz_txt", "abz_betrag",
"abz_proz") VALUES (9, 'Test Rule', 5, 6);
UPDATE "abzu_ruletest" SET "abz_betrag"= 3 WHERE "abz_id"=9;
UPDATE "abzu_ruletest" SET "abz_betrag"= 3, abz_txt='Test Rule 2' WHERE
"abz_id"=9;
thanks, Daniel
From | Date | Subject | |
---|---|---|---|
Next Message | strk | 2005-03-22 09:07:40 | Re: caches lifetime with SQL vs PL/PGSQL procs |
Previous Message | Pavel Stehule | 2005-03-22 06:32:10 | Re: Proposal: OUT parameters for plpgsql |