Re: Updatable Views

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.

Our application will need to support multiple locations. Some users need
to be restricted to viewing/adding/editing data for only a single location,
while other users are more interested in consolidated information. We were
thinking that each site could be presented to the user as a view with
restrictions appropriate for his/her access level. But, that does not seem
to be an option.

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

Rich

--
Richard B. Shepard, Ph.D. | Integrity Credibility
Applied Ecosystem Services, Inc. | Innovation
<http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863

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

On Tue, 27 Nov 2007, Jeff Davis wrote:

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

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

Thanks very much, Jeff. That's very helpful.

Rich

--
Richard B. Shepard, Ph.D. | Integrity Credibility
Applied Ecosystem Services, Inc. | Innovation
<http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863

On Nov 27, 2007, at 11:00, 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.
>
> Our application will need to support multiple locations. Some
> users need
> to be restricted to viewing/adding/editing data for only a single
> location,
> while other users are more interested in consolidated information.
> We were
> thinking that each site could be presented to the user as a view with
> restrictions appropriate for his/her access level. But, that does
> not seem
> to be an option.

Following up on what Jeff said, check out my OSCON presentation on
using rules on views to simplify object-relational mapping:

http://kineticode.com/docs/polymorphic_database_design.pdf

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

MySQL's updatable views work great, as long as they are views of only
a single table. At least, that was true last time I checked.

Best,

David

On Tue, 27 Nov 2007, David E. Wheeler wrote:

> Following up on what Jeff said, check out my OSCON presentation on using
> rules on views to simplify object-relational mapping:
> http://kineticode.com/docs/polymorphic_database_design.pdf

Thank you, David, I will.

> MySQL's updatable views work great, as long as they are views of only a
> single table. At least, that was true last time I checked.

That's interesting. I appreciate your insight.

We may be able to avoid views all together; how to obtain the results we
need is still a work in progress.

Rich

--
Richard B. Shepard, Ph.D. | Integrity Credibility
Applied Ecosystem Services, Inc. | Innovation
<http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863