problem with rules - column values lost

Поиск
Список
Период
Сортировка
От Daniel Schuchardt
Тема problem with rules - column values lost
Дата
Msg-id d1ombi$58g$1@news.hub.org
обсуждение исходный текст
Ответы Re: problem with rules - column values lost  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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
ONDELETE 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 ASSELECT 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


В списке pgsql-hackers по дате отправления:

Предыдущее
От: chamil wijenayake
Дата:
Сообщение: i want to find
Следующее
От: Tom Lane
Дата:
Сообщение: Re: problem with rules - column values lost