Re: Couple Postgres View Questions

----- Original Message -----
From: "Brian Maguire" <bmaguire@vantage.com>
To: "Postgresql General Mail List" <pgsql-general@postgresql.org>
Sent: Wednesday, July 16, 2003 6:44 PM
Subject: [GENERAL] Couple Postgres View Questions


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

No, but if You really want to know how it works be prepared to spend few
months on this.
(I already spend much time with PostgreSQL sources, and I still do not know
enough).
However here is part from PostgreSQL documentation:

2.1. The Path of a Query
Here we give a short overview of the stages a query has to pass in order to
obtain a result.

  1.. A connection from an application program to the PostgreSQL server has
to be established. The application program transmits a query to the server
and receives the results sent back by the server.

  2.. The parser stage checks the query transmitted by the application
program (client) for correct syntax and creates a query tree.

  3.. The rewrite system takes the query tree created by the parser stage
and looks for any rules (stored in the system catalogs) to apply to the
querytree and performs the transformations given in the rule bodies. One
application of the rewrite system is given in the realization of views.

  Whenever a query against a view (i.e. a virtual table) is made, the
rewrite system rewrites the user's query to a query that accesses the base
tables given in the view definition instead.

  4.. The planner/optimizer takes the (rewritten) querytree and creates a
queryplan that will be the input to the executor.

  It does so by first creating all possible paths leading to the same
result. For example if there is an index on a relation to be scanned, there
are two paths for the scan. One possibility is a simple sequential scan and
the other possibility is to use the index. Next the cost for the execution
of each plan is estimated and the cheapest plan is chosen and handed back.

  5.. The executor recursively steps through the plan tree and retrieves
tuples in the way represented by the plan. The executor makes use of the
storage system while scanning relations, performs sorts and joins, evaluates
qualifications and finally hands back the tuples derived.


Pay attention to stage 3, there is a "secret" how View work.

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

PostgreSQL DOES support updatable views, but doesn't support SQL92 syntax
for updatable views.
However, rules are more powerful than updatable views. See the documentation
under "The Rule System".

Regards !