[HACKERS] problem with rules - column values lost

Daniel Schuchardt Sat, 26 Mar 2005 22:50:14 -0800

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=# 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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[HACKERS] problem with rules - column values lost

Reply via email to