Re: Updatable Views

On Tue, 2007-11-27 at 11:00 -0800, Rich Shepard wrote:
> The Douglas^2 book has only a couple of pages on views, and my partner's
> research taught him that postgres does not support updatable views. This
> surprises me.

PostgreSQL supports updatable views through a more generalized mechanism
called rules. You actually have more flexibility with rules, and can
make some views updatable that you can't in other databases.

It's more of a challenge to make updatable views in PostgreSQL because
you have to create several rules for each view, however, you have more
freedom about how it's handled.

Views themselves can be implemented with rules in PostgreSQL.

Another benefit is that PostgreSQL has transactional DDL, meaning that
you can replace a table by an updatable view within a transaction, so
that there's no window of time in which the table is inconsistent or
nonexistent.

There has been discussion about making simple updatable views easier to
create, like in other databases.

> Are there quick and easy ways to accomplish this with postgres?
> Apparently, MySQL5 fully supports views as functional as base tables.

Personally, I would be very wary of MySQL in this regard. I would expect
there to be many limitations of this feature that aren't immediately
obvious.

Regards,
Jeff Davis