Re: [SQL] RV: A little problem updating data with views

"Jorge Herrera Piñero" <jhpinero@ull.es> writes:
>     When we issue an update statment within a view, always obtain 'UPDATE 0'
>     and the data is not updated. We have checked write permissions on view
>     and are right.

I learned this the hard way a few weeks ago.  A view is just a table
that has an "on select" rule that rewrites select queries to do
something else than pull tuples from the view table itself.

If you want to insert into a view, you need to write an "on insert"
rule that does something appropriate with the tuples.  (Else they'll
get dumped into the underlying table, where you'll never see them
again because you can't select them --- the "on select" rule will
redirect your select queries.)

Likewise, updating a view isn't going to do anything useful unless
you provide a rule to define what it should mean (else the update is
applied to the empty table that underlies the view...)

The system will not provide these rules for you because in the
general case there's no way to automatically decide what to do
(if the view is made by a complicated select, there may not *be*
any real tuples that could be updated...)  But if you can figure
out a reasonable interpretation for modifications to the view,
you can write a rule that does it.

I think in 6.6, Jan is going to provide default insert/update rules for
views that will report an error, so that you're not left wondering what
the heck happened to your query.

Anyway, the bottom line is to go read up on query-rewrite rules.
        regards, tom lane