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