Обсуждение: Problem with rules and conditions
Hello, I have a problem with rules in postgres, it may be a bug, or maybe I'm doing something wrong. I'm running version 7.1.2 on a freebsd 4.3 box. Here is my table: CREATE TABLE customer ( cono integer not null, Name varchar, ssn varchar(10), PRIMARY KEY (cono) ); Here is the rule: CREATE RULE constraint_customer_ssn_insert AS ON INSERT TO customer WHERE NOT new.ssn IS NULL DO INSTEAD INSERT INTO customer (cono,name) VALUES (new.cono,new.name); When I execute "insert into customer values (1,'bogus',null);" the result is "ERROR: query rewritten 10 times, may contain cycles" is appeared. Is this suppose to trigger my rule? The condition is not fullfilled, the ssn value is null in the insert query. To me it seems like the where clause is skipped somehow... Can anybody help me find out why? Thanks, Tobias Hermansson, MSc Student, University of Sk�vde, Sweden _________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.
Tobias Hermansson wrote: > Hello, > > I have a problem with rules in postgres, it may be a bug, or maybe I'm doing > something wrong. I'm running version 7.1.2 on a freebsd 4.3 box. > > Here is my table: > > CREATE TABLE customer ( > cono integer not null, > Name varchar, > ssn varchar(10), > PRIMARY KEY (cono) > ); > > Here is the rule: > > CREATE RULE constraint_customer_ssn_insert > AS ON INSERT > TO customer > WHERE NOT new.ssn IS NULL > DO INSTEAD > INSERT INTO customer (cono,name) VALUES (new.cono,new.name); > > When I execute "insert into customer values (1,'bogus',null);" the result is > "ERROR: query rewritten 10 times, may contain cycles" is appeared. > > Is this suppose to trigger my rule? The condition is not fullfilled, the ssn > value is null in the insert query. To me it seems like the where clause is > skipped somehow... > > Can anybody help me find out why? So you allways want to set customer.ssn to NULL on insert, right? You cannot have a rule action that does the same operation (INSERT) on the same table (customer). This triggers thesame rule to get fired again, and that's an endless *rewrite* loop. Note that the rewriting doesn't look atthe values, it allways splits the parsetree in your above rule and has to apply the same rule on the new query again. Use a trigger instead: CREATE FUNCTION cust_ssn_ins () RETURNS opaque AS ' BEGIN NEW.ssn := NULL; RETURN NEW; END;' LANGUAGE 'plpgsql'; CREATE TRIGGER cust_ssn_ins BEFORE INSERT TO customer FOR EACH ROW EXECUTE PROCEDURE cust_ssn_ins(); Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com # _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com