conditional rule not applied

From: Seb <spluque(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: conditional rule not applied
Date: 2009-12-31 01:39:15
Message-ID: 87ws03dirg.fsf@kolob.sebmags.homelinux.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Seb 2009-12-31 02:04:51 Re: conditional rule not applied
Previous Message Craig Ringer 2009-12-31 01:13:16 Re: Visual DATA editor for PostgreSQL?