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.
--- Begin Message ---
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
--- End Message ---
--- Begin Message ---
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-gene...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--- End Message ---
--- Begin Message ---
On Wed, 30 Dec 2009 19:39:15 -0600,
Seb <splu...@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-gene...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--- End Message ---
--- Begin Message ---
On Wed, 30 Dec 2009 20:04:51 -0600,
Seb <splu...@gmail.com> wrote:
> On Wed, 30 Dec 2009 19:39:15 -0600,
> Seb <splu...@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-gene...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--- End Message ---
--- Begin Message ---
On Tue, 05 Jan 2010 20:20:13 -0600,
Seb <splu...@gmail.com> wrote:
> On Wed, 30 Dec 2009 20:04:51 -0600,
> Seb <splu...@gmail.com> wrote:
> On Wed, 30 Dec 2009 19:39:15 -0600,
>> Seb <splu...@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-gene...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--- End Message ---
--- Begin Message ---
On Wed, 06 Jan 2010 09:39:45 -0600,
Seb <splu...@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-gene...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--- End Message ---
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
