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(at)flame(dot)co(dot)za]
>> Sent: Tuesday, November 30, 1999 12:25 PM
>> To: hackers(at)postgreSQL(dot)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
>>
>> ************
>>