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

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org