Re: Problem with rules and conditions

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