Thread: Inserting into views

Is this allowed in Postgresql?

It "appears" to work, but then doesn't.

R.
----
test=# create table liar (num integer, name varchar(20));
CREATE
test=# insert into liar values (32, 'Bill');
INSERT 31754 1
test=# insert into liar values (2, 'Joey');
INSERT 31755 1
test=# insert into liar values (6, 'Felicia');
INSERT 31756 1
test=# select * from liar;
 num |  name
-----+---------
  32 | Bill
   2 | Joey
   6 | Felicia
(3 rows)

test=# create view liar_view as select num, name, num as fodder from liar;
CREATE 31768 1
test=# SELECT * from liar_view ;
 num |  name   | fodder
-----+---------+--------
  32 | Bill    |     32
   2 | Joey    |      2
   6 | Felicia |      6
(3 rows)

test=# insert into liar_view (num, name) values (43, 'Jane');
INSERT 31769 1
test=# SELECT * from liar_view ;
 num |  name   | fodder
-----+---------+--------
  32 | Bill    |     32
   2 | Joey    |      2
   6 | Felicia |      6
(3 rows)

test=# select * from liar;
 num |  name
-----+---------
  32 | Bill
   2 | Joey
   6 | Felicia
(3 rows)

test=# insert into liar_view (num, name, fodder) values (43, 'Jane', 43);
INSERT 31770 1

Richard Harvey Chapman <hchapman@3gfp.com> writes:
> [ what happened to the data he inserted into a view? ]

Perhaps that ought to go in the FAQ ... I know I fell into the same
trap awhile back.  The thing you have to realize is that a view is
just an ON SELECT DO INSTEAD rule --- when you select from the view
table, the query gets rewritten to do something else instead.  But
Postgres doesn't make any attempt to deduce what an appropriate
inverse function might be for the ON SELECT transformation.  If
you want to insert/update/delete on the view, you have to provide
ON INSERT/UPDATE/DELETE rules that show how to do the right things
to the underlying tables.

What actually happens if you try an INSERT without an ON INSERT rule?
Well, it inserts all right, into the table that the view is founded
on.  There is actually a table there underneath, though it's normally
empty.  You can insert into it ... but you can't see the data because
any attempt to select from it gets rewritten into the view query!

We've talked about the idea of adding error checks that refuse to let
you insert/update/delete in tables that have ON SELECT DO INSTEAD
rules, unless you've provided a rule that substitutes for the query.
Hasn't been changed yet, partly because people are worried whether there
might be a legitimate reason for having an ON SELECT DO INSTEAD rule
on a "real" table.  I haven't seen an example yet though...

            regards, tom lane