Re: Couple Postgres View Questions

"Brian Maguire" <bmaguire@vantage.com> writes:

> I have a couple questions about Postgres and Views.
>
> I am curious about what Postgres actually does in regards to views and
> the internal steps that it takes.
>
> Are these the steps that it takes for each query that includes a view?
> 1. Dynamically Creates a Temp table with the view's select statement.
> 2. Executes the sql command against the view
> 3. Postgres destroys the temp table.

This is not the way it works at all.  Views are implemented using
SQL rewrite rules, which are a PG feature.  So here's what actually
happens:

1.  The user runs a query against a view.
2.  The query parser changes the query using the rule, into a query
    against the underlying table(s).
3.  The query is optimized and executed.

> I understand that Postgres is not SQL 92 compliant with views because it
> does not support updatable views.  Are there plans ever to support
> updatable views?

You can make any PG view updatable by defining ON INSERT and/or ON
UPDATE rules for it, on a case-by-case basis.  The SQL2 updatable
views are theoretically implementable--there's been talk about it--but
are not currently there.

See the documentation for CREATE RULE and CREATE VIEW.

-Doug