pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedwritable joined view
3+ messages / 3 participants
[nested] [flat]
* writable joined view
@ 2005-10-19 01:10 Sarah Asmaels <sarah@spiesonline.net>
2005-10-25 12:07 ` Re: writable joined view Wiebe Cazemier <halfgaar@gmail.com>
2005-10-25 14:49 ` Re: writable joined view Richard Huxton <dev@archonet.com>
0 siblings, 2 replies; 3+ messages in thread
From: Sarah Asmaels @ 2005-10-19 01:10 UTC (permalink / raw)
To: pgsql-sql
Hi!
I have one table referencing an object in another table through an ID,
and a view joining those tables on the ID. I want to create rules to
rewrite updates/deletes/inserts on the joined view to act on the real
tables. Can you give me some pointers? The documentation has only
examples for views depending on single tables.
Thank you,
Sarah
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: writable joined view
2005-10-19 01:10 writable joined view Sarah Asmaels <sarah@spiesonline.net>
@ 2005-10-25 12:07 ` Wiebe Cazemier <halfgaar@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Wiebe Cazemier @ 2005-10-25 12:07 UTC (permalink / raw)
To: Sarah Asmaels <sarah@spiesonline.net>; +Cc: pgsql-sql
Sarah Asmaels wrote:
> Hi!
>
> I have one table referencing an object in another table through an ID,
> and a view joining those tables on the ID. I want to create rules to
> rewrite updates/deletes/inserts on the joined view to act on the real
> tables. Can you give me some pointers? The documentation has only
> examples for views depending on single tables.
>
> Thank you,
>
> Sarah
Is there any difference in multi or single table view? When you create a rule,
you have access to NEW and/or OLD, which will contain all the fields your view
has. You can then do something like this:
CREATE RULE insert_rule AS ON INSERT TO your_view DO INSTEAD (
INSERT INTO table1 (name) VALUES (NEW.name);
INSERT INTO table2 (favorite_color) VALUES (NEW.favorite_color);
);
Or is there something I'm not understanding about your request, or perhaps rules
in general?
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: writable joined view
2005-10-19 01:10 writable joined view Sarah Asmaels <sarah@spiesonline.net>
@ 2005-10-25 14:49 ` Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 3+ messages in thread
From: Richard Huxton @ 2005-10-25 14:49 UTC (permalink / raw)
To: Sarah Asmaels <sarah@spiesonline.net>; +Cc: pgsql-sql
Sarah Asmaels wrote:
> Hi!
>
> I have one table referencing an object in another table through an ID,
> and a view joining those tables on the ID. I want to create rules to
> rewrite updates/deletes/inserts on the joined view to act on the real
> tables. Can you give me some pointers? The documentation has only
> examples for views depending on single tables.
I've attached a small example script that shows insert/update/delete on
a "joined" view.
--
Richard Huxton
Archonet Ltd
Attachments:
simple_joined_view_test_script.sql (text/x-sql, 2.4K) download
-- Rules on joined tables
-- Below are two tables: contact, contact_emails
-- Email addresses with a priority of 0 are considered "default"
-- Contacts can be either personal (PNL) or business (BUS)
--
BEGIN;
CREATE TABLE contacts (
id int4 NOT NULL UNIQUE,
full_name varchar(100),
con_type varchar(3) NOT NULL DEFAULT ('PNL') CHECK (con_type IN ('PNL','BUS')),
PRIMARY KEY (id)
);
CREATE TABLE contact_emails (
contact int4 NOT NULL REFERENCES contacts,
pri int2 CHECK (pri >= 0),
email varchar(100),
PRIMARY KEY (contact, pri)
);
COPY contacts (id,full_name,con_type) FROM stdin;
1 Aaron Aardvark PNL
2 Betty Bee PNL
3 Carl Cat PNL
4 Deputy Dawg BUS
5 Eric Elephant BUS
6 Fran Fish BUS
\.
COPY contact_emails (contact,pri,email) FROM stdin;
1 0 aaron@hotmail.com
1 1 aaron@aardvarks.com
2 0 betty@bees.com
3 0 carl@gmail.com
4 0 deputy@hotmail.com
4 1 deputy@gmail.com
5 0 eric@hotmail.com
6 0 fran@hotmail.com
\.
COMMIT;
-- contact_defaults
-- A view that shows the default email for each contact.
-- There are rules that allow updating of the view.
-- Note how when deleting, we ignore "pri", but when updating we make sure it is set to 0
-- Obviously, we could have handled deleting emails through a FK cascade.
--
BEGIN;
CREATE VIEW contact_defaults AS
SELECT
c.id AS con_id,
c.full_name,
c.con_type,
e.email
FROM
contacts c,
contact_emails e
WHERE
c.id = e.contact
AND e.pri = 0
;
CREATE OR REPLACE RULE con_def_del AS ON DELETE TO contact_defaults
DO INSTEAD (
DELETE FROM contact_emails WHERE contact = OLD.con_id;
DELETE FROM contacts WHERE id = OLD.con_id;
);
CREATE OR REPLACE RULE con_def_upd AS ON UPDATE TO contact_defaults
DO INSTEAD (
UPDATE contact_emails SET email=NEW.email WHERE contact=OLD.con_id AND pri=0;
UPDATE contacts SET full_name=NEW.full_name, con_type=NEW.con_type WHERE id=OLD.con_id;
);
CREATE OR REPLACE RULE con_def_ins AS ON INSERT TO contact_defaults
DO INSTEAD (
INSERT INTO contacts (id,full_name,con_type) VALUES (NEW.con_id, NEW.full_name, NEW.con_type);
INSERT INTO contact_emails (contact,pri,email) VALUES (NEW.con_id, 0, NEW.email);
);
COMMIT;
-- Below are some queries to update the view and show what happens.
--
BEGIN;
SELECT * FROM contact_defaults ORDER BY con_id;
UPDATE contact_defaults SET con_type='BUS' WHERE con_id<4;
SELECT * FROM contact_defaults ORDER BY con_id;
UPDATE contact_defaults SET email=email || 'x' WHERE con_id>4;
SELECT * FROM contact_defaults ORDER BY con_id;
COMMIT;
end of thread, other threads:[~2005-10-25 14:49 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-19 01:10 writable joined view Sarah Asmaels <sarah@spiesonline.net>
2005-10-25 12:07 ` Wiebe Cazemier <halfgaar@gmail.com>
2005-10-25 14:49 ` Richard Huxton <dev@archonet.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