Thread

1. RE: [HACKERS] Insert into view

   Ansley, Michael <michael.ansley@intec.co.za> — 1999-11-30T12:49:26Z

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

2. Re: [HACKERS] Insert into view

   Theo Kramer <theo@flame.co.za> — 1999-11-30T15:34:12Z

   "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