To: pgsql-sql@postgresql.org
From: Seb <spluque@gmail.com>
Subject: Fwd: conditional rule not applied
Date: Wed, 06 Jan 2010 14:40:46 -0600
Organization: Church of Emacs
Lines: 370
Message-ID: <87fx6jm0fl.fsf@kolob.sebmags.homelinux.org>
Mime-Version: 1.0
Content-Type: multipart/mixed; boundary="=-=-="
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/23.1 (gnu/linux)
Cancel-Lock: sha1:DxoF8lp0/px8ugTw3gzTEXU+o2M=

--=-=-=
Content-Type: text/plain

Hi,

Apologies for posting this from postgresql.general, but this failed to
get any follow-ups in that NG.  Hopefully someone here can shed some
light on this.


--=-=-=
Content-Type: multipart/digest; boundary="==-=-="

--==-=-=
Content-Type: message/rfc822

Subject: Topics
MIME-Version: 1.0
Content-Type: text/plain

Topics:
   conditional rule not applied
   Re: conditional rule not applied
   Re: conditional rule not applied
   Re: conditional rule not applied
   Re: conditional rule not applied

--==-=-=
Content-Type: message/rfc822

Date: Wed, 30 Dec 2009 19:39:15 -0600
From: Seb <spluque@gmail.com>
To: pgsql-general@postgresql.org
Subject: conditional rule not applied
Message-ID: <87ws03dirg.fsf@kolob.sebmags.homelinux.org>
MIME-Version: 1.0
Content-Type: text/plain

Hi,

I'm trying to create a rule to be applied on update to a view that
consists of two joined tables.  Table 'shoes' below is left-joined with
table 'shoelaces' in the view 'footwear'.  I'd like to create a simple
update rule on the view, only if the value of a common column
corresponds to an inexistent record in 'shoelaces', so the result is an
INSERT into 'shoelaces' with the new record:

---<--------------------cut here---------------start------------------->---
CREATE TABLE shoes (
	sh_id serial PRIMARY KEY,
	sh_name text,
	sh_avail integer
);

CREATE TABLE shoelaces (
	sl_id serial PRIMARY KEY,
	sh_id integer REFERENCES shoes,
	sl_name text
);

INSERT INTO shoes (sh_name, sh_avail)
    VALUES ('sh1', 2), ('sh2', 0), ('sh3', 4), ('sh4', 3);

INSERT INTO shoelaces (sh_id, sl_name)
    VALUES (1, 'sl1'), (3, 'sl2');

SELECT * FROM shoes;

 sh_id | sh_name | sh_avail 
-------+---------+----------
     1 | sh1     |        2
     2 | sh2     |        0
     3 | sh3     |        4
     4 | sh4     |        3

SELECT * FROM shoelaces;

 sl_id | sh_id | sl_name 
-------+-------+---------
     1 |     1 | sl1
     2 |     3 | sl2
(2 rows)

CREATE VIEW footwear AS
SELECT sh.sh_id, sh_name, sh_avail, sl_name
FROM shoes sh LEFT JOIN shoelaces sl USING (sh_id);

SELECT * FROM footwear;

 sh_id | sh_name | sh_avail | sl_name 
-------+---------+----------+---------
     1 | sh1     |        2 | sl1
     2 | sh2     |        0 | 
     3 | sh3     |        4 | sl2
     4 | sh4     |        3 | 
(4 rows)

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);

-- Testing: result should be a new record in 'shoelaces'
UPDATE footwear SET sl_name = 'sl3' WHERE sh_name = 'sh2';

-- but that doesn't happen:
SELECT * FROM shoelaces;

 sl_id | sh_id | sl_name 
-------+-------+---------
     1 |     1 | sl1
     2 |     3 | sl2
(2 rows)
---<--------------------cut here---------------end--------------------->---

Any tips would be much appreciated.

-- 
Seb


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


--==-=-=
Content-Type: message/rfc822

Date: Wed, 30 Dec 2009 20:04:51 -0600
From: Seb <spluque@gmail.com>
To: pgsql-general@postgresql.org
Subject: Re: conditional rule not applied
Message-ID: <87skardhks.fsf@kolob.sebmags.homelinux.org>
References: <87ws03dirg.fsf@kolob.sebmags.homelinux.org>
MIME-Version: 1.0
Content-Type: text/plain

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?


-- 
Seb


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


--==-=-=
Content-Type: message/rfc822

Date: Tue, 05 Jan 2010 20:20:13 -0600
From: Seb <spluque@gmail.com>
To: pgsql-general@postgresql.org
Subject: Re: conditional rule not applied
Message-ID: <87bph8otya.fsf@kolob.sebmags.homelinux.org>
References: <87ws03dirg.fsf@kolob.sebmags.homelinux.org>
	<87skardhks.fsf@kolob.sebmags.homelinux.org>
MIME-Version: 1.0
Content-Type: text/plain

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.


-- 
Seb


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


--==-=-=
Content-Type: message/rfc822

Date: Wed, 06 Jan 2010 09:39:45 -0600
From: Seb <spluque@gmail.com>
To: pgsql-general@postgresql.org
Subject: Re: conditional rule not applied
Message-ID: <87pr5nnsxq.fsf@kolob.sebmags.homelinux.org>
References: <87ws03dirg.fsf@kolob.sebmags.homelinux.org>
	<87skardhks.fsf@kolob.sebmags.homelinux.org>
	<87bph8otya.fsf@kolob.sebmags.homelinux.org>
MIME-Version: 1.0
Content-Type: text/plain

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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


--==-=-=
Content-Type: message/rfc822

Date: Wed, 06 Jan 2010 10:43:16 -0600
From: Seb <spluque@gmail.com>
To: pgsql-general@postgresql.org
Subject: Re: conditional rule not applied
Message-ID: <87iqbfnpzv.fsf@kolob.sebmags.homelinux.org>
References: <87ws03dirg.fsf@kolob.sebmags.homelinux.org>
	<87skardhks.fsf@kolob.sebmags.homelinux.org>
	<87bph8otya.fsf@kolob.sebmags.homelinux.org>
	<87pr5nnsxq.fsf@kolob.sebmags.homelinux.org>
MIME-Version: 1.0
Content-Type: text/plain

On Wed, 06 Jan 2010 09:39:45 -0600,
Seb <spluque@gmail.com> wrote:

> 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);

Adding to my confusion here, is the fact that the rule above seems to
work well, even though the docs say:

---<--------------------cut here---------------start------------------->---
condition

 Any SQL conditional expression (returning boolean). The condition
 expression cannot refer to any tables except NEW and OLD, and cannot
 contain aggregate functions.
---<--------------------cut here---------------end--------------------->---

So the WHERE condition in the rule above should not be allowed since it
does reference a table other than NEW and OLD in the EXISTS statement.
Any enlightening comments appreciated.


-- 
Seb


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


--==-=-=--

--=-=-=--