conditional rule not applied
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
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
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
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
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
On Wed, Dec 30, 2009 at 6:39 PM, 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);
Isn't that first rule gonna always fire and make the second one a NOOP?
On Thu, 7 Jan 2010 21:04:45 -0700,
Scott Marlowe <scott.marlowe@gmail.com> wrote:
On Wed, Dec 30, 2009 at 6:39 PM, 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);
Isn't that first rule gonna always fire and make the second one a
NOOP?
No, the second is an implied ALSO, so it gets added to the DO INSTEAD
NOTHING. This is actually the approach recommended in the man page for
CREATE RULE where the reasons for doing that are described. The problem
with this is that it always displays the message "UPDATE 0" when in fact
the second rule may have also been applied with the INSERT. I posted
this question to the postgresql.sql NG, where some discussion ensued.
--
Seb