Re: conditional rule not applied

On Tue, 05 Jan 2010 20:20:13 -0600,
Seb <spluque@gmail.com> wrote:

> On Wed, 30 Dec 2009 20:04:51 -0600,
> Seb <spluque@gmail.com> wrote:

> On Wed, 30 Dec 2009 19:39:15 -0600,
>> Seb <spluque@gmail.com> wrote:

> CREATE RULE footwear_nothing_upd AS
>>> ON UPDATE TO footwear DO INSTEAD NOTHING; CREATE RULE
>>> footwear_newshoelaces_upd AS ON UPDATE TO footwear WHERE NEW.sl_name
>>> <> OLD.sl_name AND OLD.sl_name IS NULL DO INSERT INTO shoelaces
>>> (sh_id, sl_name) VALUES(NEW.sh_id, NEW.sl_name);

>> I think my error is in the test expression, which doesn't deal
>> properly with the null value, so correcting:

>> CREATE RULE footwear_nothing_upd AS
>>     ON UPDATE TO footwear DO INSTEAD NOTHING;
>> CREATE RULE footwear_newshoelaces_upd AS
>>     ON UPDATE TO footwear
>>     WHERE NEW.sl_name IS DISTINCT FROM OLD.sl_name AND OLD.sl_name IS NULL
>>     DO
>> INSERT INTO shoelaces (sh_id, sl_name)
>>     VALUES(NEW.sh_id, NEW.sl_name);

>> However, could a more direct and robust test for an inexistent record
>> in 'shoelaces' be made?

> Any ideas? I'm not sure this is the best way to test whether the
> record to update corresponds to a inexistent record in
> 'shoelaces'. Thanks.

Would this express the intention any better?

CREATE RULE footwear_nothing_upd AS
    ON UPDATE TO footwear DO INSTEAD NOTHING;
CREATE RULE footwear_newshoelaces_upd AS
    ON UPDATE TO footwear
    WHERE NOT EXISTS (SELECT sh_id FROM shoelaces WHERE NEW.sh_id=shoelaces.sh_id)
    DO
INSERT INTO shoelaces (sh_id, sl_name)
    VALUES(NEW.sh_id, NEW.sl_name);


--
Seb