RE: [HACKERS] Insert into view

I think this was covered a little while back, but it runs something like
this:  a view is a relation, with a select rule (which is the view query).
When you insert into the view (which, like I said, is just another relation,
it actually inserts into the view relation.  However, when you select from
it, of course, the select rule fires, and you don't see any of the
information.  I suppose you could set up a nice insert rule to insert into
the base tables of the query if you wanted.  I normally do this through
stored procs, but this would be essentially the same thing, just nicer
client-side SQL.

I suppose that views could be made so that a tuple insert would fail, but
you should know your db better ;-)

MikeA

>> -----Original Message-----
>> From: Theo Kramer [mailto:theo@flame.co.za]
>> Sent: Tuesday, November 30, 1999 12:25 PM
>> To: hackers@postgreSQL.org
>> Subject: [HACKERS] Insert into view
>> 
>> 
>> Any thoughts on the following
>> 
>> ------------------------------ testview.sql
>> -------------------------------------
>> drop table testhead; /* If it exists */
>> drop table testline; /* If it exists */
>> drop view testview; /* If it exists */
>> 
>> create table testhead (
>>   part text
>> );
>> 
>> create table testline (
>>   part text,
>>   colour text,
>>   adate datetime default 'now'
>> );
>> 
>> create view testview as
>>   select testhead.part, testline.colour, testline.adate from 
>> testhead, testline
>>   where testhead.part = testline.part;
>> 
>> insert into testview values  ('pen', 'green');
>> insert into testview values  ('pen', 'blue');
>> insert into testview values  ('pen', 'black');
>> 
>> select * from testview;
>> 
>> -------------------------------------------------------------
>> ----------------------
>> 
>> The inserts report no errors, and when looking into 
>> $PGDATA/base/mydb/testview
>> with a hex editor I can see the values inserted. 
>> 
>> The select on view returns nothing... 
>> 
>> Should the insert not fail seeing that views are read only ?
>> 
>> -- 
>> --------
>> Regards
>> Theo
>> 
>> ************
>>