To: pgsql-sql@postgresql.org
From: Seb <spluque@gmail.com>
Subject: updateable/insertable view having left joined tables
Date: Fri, 12 Jun 2009 16:35:09 -0500
Organization: Church of Emacs
Lines: 73
Message-ID: <87prd9t9te.fsf@kolob.sebmags.homelinux.org>
Mime-Version: 1.0
Content-Type: text/plain
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/23.0.94 (gnu/linux)
Cancel-Lock: sha1:V2dRxfZX2fO6enxCSqRShgtRc2c=

Hi,

I'm taking a first foray into writing rules, and am struggling with one
for a view that has a left joined table:

---<--------------------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);
INSERT INTO shoes (sh_name, sh_avail) VALUES ('sh2', 0);
INSERT INTO shoes (sh_name, sh_avail) VALUES ('sh3', 4);
INSERT INTO shoes (sh_name, sh_avail) VALUES ('sh4', 3);

INSERT INTO shoelaces (sh_id, sl_name) VALUES (1, 'sl1');
INSERT INTO shoelaces (sh_id, sl_name) VALUES (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

-- We create a view that could be used to easily insert data into
-- shoelaces table:

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

SELECT * FROM shoe;
 sh_id | sh_name | sh_avail | sl_name 
-------+---------+----------+---------
     1 | sh1     |        2 | sl1
     2 | sh2     |        0 | 
     3 | sh3     |        4 | sl2
     4 | sh4     |        3 | 
---<--------------------cut here---------------end--------------------->---

Say I want to update this view like:

UPDATE shoe SET sl_name = 'sl3' WHERE sh_id = 2;

The right (well, to me) thing to do would be to insert a row in
shoelaces like this:

INSERT INTO shoelaces (sh_id, sl_name) VALUES (2, 'sl3');

Of course, if the update involves a row that is already available from
shoelaces, it would be an update on at least one of the tables, rather
than an insert on shoelaces.  I'm not sure how a rule to do this would
look like, so any pointers would be appreciated.  Thanks.


-- 
Seb