Greetings.
I love PostgreSQL's support of automatically updatable views, limited
though it is. I would like to point out what I believe is another
case where views can be updated, without ambiguity. I'm going to call
this a "foreign key view". For example, given a view V which joins a
table C with the tables it references with foreign keys, C could be
updated through V.
I'll start with a concrete example, for the sake of simplicity.
create table users (
id serial primary key,
name text unique not null
);
create table phone_numbers (
id serial primary key,
user_id integer not null references users,
number text not null
);
CREATE VIEW show_phone_numbers AS SELECT u.name, p.number FROM
phone_numbers p JOIN users u ON p.user_id = u.id;
Here show_phone_numbers would be an updatable view. A conservative
approach would allow entries from phone_numbers to be created,
updated, or deleted though show_phone_numbers, but leave the users
table alone. It might follow these rules:
1) An insert into a foreign key view is allowed if the values for the
fields from the parent tables match existing records in those tables.
2) An update to a foreign key view is allowed if the new values are
only for fields within the child table.
3) A delete from a foreign key view removes records only from the child table.
Though allowing effects in the parent tables may also, with proper
consideration, be viable, I believe that even the limited, fairly
clear case of keeping effects in the child would be extremely useful
in making normalized schemata easier to work with.
To put the idea more generally, let me give myself a little syntax
(I'm not proposing new syntax; just trying to get the idea across).
Let:
foreign_key_join(x)
Mean:
SELECT * FROM (x NATURAL LEFT JOIN foreign_key_view(y1) NATURAL LEFT
JOIN foreign_key_join(y2)...)
/* Note the recursion */
Where (y1, y2, y3...) are the tables x references, and we assume that
the constrained and constraining fields in each table have the same
names (just for the sake of being able to express the idea in SQL).
Then:
CREATE VIEW v as foreign_key_join(x);
Would be updatable. As, of course, would a more limited view joining
on some of the foreign keys, or joining to parents without joining to
their parents.
Any thoughts on this would be welcome. This is something which I
would personally find exceptionally valuable; if there are problems
with the idea, I'd like to know. As well, if my description isn't
clear enough I'd be happy to explain.
--
Ray Brinzer