Updateable views - Mailing list pgsql-hackers

Hi,

I'm currently working with Bernd in an implementation
of updateable views and want to know the hacker's
opinion on this issue.

What features have to be implemented in a first
extension in order to the patch to be accepted? What
features can wait until a second extension?

This are my first thought on this (i start working on
this just two weeks ago).

***************** thoughts *******************
- What if we cannot create one of the three rules?   Make the rule not updateable at all?   Or create the rules we can?
(ithink this is the   correct)
 


General Restrictions!!!
---------------------------
- The column target list holds column fields only,  that are retrieved from one base relation / view  only. (NO joined
views).
- UNION [ALL]/EXCEPT, DISTINCT and GROUP BY query  expressions aren't updateable at all. 
- HAVING, Aggregates, function expressions and  Subqueries aren't allowed to be updateable, too

NOTE: one option is add a catalog that contains info      about updateability of the view attributes, just     like
ORACLE'suser_updateable_column view      (actually pg_attribute says what columns has a      view, can it be
extended?).     That way we can have views in which some columns
 
     are updateable and other are not. Views with      more complicated querys (even joined ones) can      be allowed
thisway.
 


Insertable???
----------------------
We need to provide, at least, a value for every column
in the underlaying table that is NOT NULL and do not 
have a DEFAULT value.

- If primary key of the table is a serial we can  manage it  CREATE RULE "ins_people_full" as ON INSERT TO people_full
DOINSTEAD
 
(  INSERT INTO people (person_id, inits, fname)   VALUES (nextval('people_person_id_seq'),NEW.inits,
NEW.fname);    INSERT INTO addresses (person_id,city, state, zip)   VALUES (currval('people_person_id_seq'), NEW.city,
NEW.state, NEW.zip);
);

- What if we add a new not null column without a  default value to the underlaying table? The insert  rule must be
deleted?

Updateable???
----------------------


Deleteable???
----------------------  
- Can we delete a row from the underlaying table if  the view where i execute the delete stmnt does not  view all the
columnsin that table?
 

- What about joined views? What is deleted?  Consider:   CREATE VIEW people_full AS   SELECT p.*, a.city, a.state,
s.state_long,          a.country, a.zip     FROM people p JOIN addresses a USING (person_id)   JOIN states s USING
(state);
 
  The a.city, a.state, s.state_long, a.country, a.zip  columns must be deleted as well as the p.* columns

***********************************

- Other point is: some people will not be happy    with updateable views, they will want their views to
 be read-only. Should we have an extension to the sql
 specs for this? Something like a READONLY keyword?

The patch Bernd did, actually covers some of this
points but is just for *very, very* simple views. We
want improve it.

These of course are just general ideas, and we really
want to know your opinion.

regards,
Jaime Casanova

_________________________________________________________
Do You Yahoo!?
Información de Estados Unidos y América Latina, en Yahoo! Noticias.
Visítanos en http://noticias.espanol.yahoo.com