pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedupdateable/insertable view having left joined tables
2+ messages / 1 participants
[nested] [flat]
* updateable/insertable view having left joined tables
@ 2009-06-12 21:35 Seb <spluque@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Seb @ 2009-06-12 21:35 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: updateable/insertable view having left joined tables
@ 2009-06-13 00:22 Seb <spluque@gmail.com>
parent: Seb <spluque@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Seb @ 2009-06-13 00:22 UTC (permalink / raw)
To: pgsql-sql
The following seems to work, but I don't feel confident this is really
the correct code:
CREATE RULE shoe_upd AS
ON UPDATE TO shoe
DO INSTEAD (
UPDATE shoes
SET sh_name = NEW.sh_name, sh_avail = NEW.sh_avail
WHERE shoes.sh_id = NEW.sh_id;
INSERT INTO shoelaces (sh_id, sl_name)
SELECT New.sh_id, New.sl_name WHERE New.sl_name IS NOT NULL;);
I could find out doing a number of tests, but it would be good to
understand what is going on.
--
Seb
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2009-06-13 00:22 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-06-12 21:35 updateable/insertable view having left joined tables Seb <spluque@gmail.com>
2009-06-13 00:22 ` Seb <spluque@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox