Re: Couple Postgres View Questions

"Brian Maguire" <bmaguire(at)vantage(dot)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