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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
