Thread: 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
>> 
>> ************
>> 

"Ansley, Michael" <Michael.Ansley@intec.co.za> writes:
> I suppose that views could be made so that a tuple insert would fail,

I think Jan muttered something about emitting a warning notice for an
attempt to store into a table that has an ON SELECT DO INSTEAD rule but
no ON INSERT rule --- which would imply that you'll never be able to
see the data you're inserting.

This mistake has bitten enough people (including me ;-)) that it seems
a warning might be a good idea.  I'm not sure if I want it to be a hard
error though.  Are there any cases where it'd make sense to allow this?
        regards, tom lane

"Ansley, Michael" wrote:
> 
> 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.

Hmm, interesting.

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

That I do, just thought a message might assist those that don't :)

--------
Regards
Theo