Thread: problem with rules - column values lost

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

Daniel Schuchardt <daniel_schuchardt@web.de> writes:
> 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.

You can't seriously expect that example to work.  The DELETE removes the
row that lang_abzu() needs to have in order to yield a non-null result,
and so the reference to new.abz_txt in the next line yields a null.

new.* and old.* in rules are macros; they don't represent some sort of
internally held data, but re-evaluations of the relevant definitions.
In particular, new.abz_txt in the last line of the rule references
the view definition if the invoking UPDATE didn't specify any particular
new value for the column.

It might be that you could get the effect you want with triggers ...
        regards, tom lane