Re: INSERT/UPDATE in views fail silently

Johann Uhrmann <juhrman@fh-landshut.de> writes:
> on my linux-systems a write access to views fails without any
> error message:

> hans=> create table demo (i integer);
> CREATE
> hans=> create view demoview as select * from demo;
> CREATE
> hans=> insert into demoview values (5);
> INSERT 151142 1
> hans=> select * from demo;
> i
> -
> (0 rows)

Yeah, a lot of people are surprised when they first try something like
that.

> Does Postgres not support insert/update on views?

It does, but you have to write the appropriate rule to explain what
you want to happen.

> Why do I get a 'INSERT' answer from postgres when no data
> is inserted?

Actually, the data *is* inserted, but it goes into the view's underlying
table where you'll never be able to see it again.  A view in Postgres
is basically just a table with an ON SELECT DO INSTEAD rule.  (The
underlying table is normally empty, it's just there as a placeholder
for the view's catalog information.)

If you want insert/update/delete to behave usefully, you need to write
rules for ON INSERT DO INSTEAD and so forth to show what should happen.
We don't try to guess what you think they should do.

There has been some talk of rejecting insert/update/delete on a table
that has ON SELECT DO INSTEAD but no rule for the other cases.  Hasn't
been changed yet though.

            regards, tom lane