Re: Parsing of VIEW definitions

"Eric B.Ridge" <ebr(at)tcdi(dot)com> writes:
> I'm just curious...
> If you have a view:
> create foo_view as select * from foo;
> Then you query it:
> select * from foo_view;

> What, behind the scenes, actually happens?

There's a pretty good description in the Programmer's Guide:
http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/rules.html

After reading that you can try looking at it for yourself. For example:

regression=# create table foo (f1 int, f2 text);
CREATE TABLE
regression=# create view foo_view as select * from foo;
CREATE VIEW
regression=# select ev_action from pg_rewrite
regression-# where ev_class = 'foo_view'::regclass; -- before 7.3 use a join

In CVS tip I get:

({QUERY :commandType 1 :querySource 0 :utilityStmt <> :resultRelation 0 :into <> :isPortal false :isBinary false :hasAggs false :hasSubLinks false :rtable ({RTE :alias {ALIAS :aliasname *OLD* :colnames <>} :eref {ALIAS :aliasname *OLD* :colnames ("f1" "f2")} :rtekind 0 :relid 2581139 :inh false :inFromCl false :checkForRead false :checkForWrite false :checkAsUser 1} {RTE :alias {ALIAS :aliasname *NEW* :colnames <>} :eref {ALIAS :aliasname *NEW* :colnames ("f1" "f2")} :rtekind 0 :relid 2581139 :inh false :inFromCl false :checkForRead false :checkForWrite false :checkAsUser 1} {RTE :alias <> :eref {ALIAS :aliasname foo :colnames ("f1" "f2")} :rtekind 0 :relid 2581134 :inh true :inFromCl true :checkForRead true :checkForWrite false :checkAsUser 1}) :jointree {FROMEXPR :fromlist ({RANGETBLREF :rtindex 3}) :quals <>} :rowMarks () :targetList ({TARGETENTRY :resdom {RESDOM :resno 1 :restype 23 :restypmod -1 :resname f1 :ressortgroupref 0 :reskey 0 :reskeyop 0 :resjunk false} :expr!
{VAR :varno 3 :varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 3 :varoattno 1}} {TARGETENTRY :resdom {RESDOM :resno 2 :restype 25 :restypmod -1 :resname f2 :ressortgroupref 0 :reskey 0 :reskeyop 0 :resjunk false} :expr {VAR :varno 3 :varattno 2 :vartype 25 :vartypmod -1 :varlevelsup 0 :varnoold 3 :varoattno 2}}) :groupClause <> :havingQual <> :distinctClause <> :sortClause <> :limitOffset <> :limitCount <> :setOperations <> :resultRelations ()})

which is a flattened textual representation of the internal querytree
data structure that represents the SELECT. The above-cited
documentation would give you a rough idea about the parts of this data
structure, and you can find the details in src/include/nodes/*.h
(mostly primnodes.h and parsenodes.h for stuff appearing in a Query
tree).

> Is the definition of "foo_view" looked up in the system catalog, query
> rewritten, parsed, then executed?

The Query representation is rebuilt from this textual form and then
substituted into the referencing query. I wouldn't call that "parsing",
though --- the work of the parser phase is long over. See
http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/overview.html

> In other words, does the query *literally* get rewritten as:
> select * from (select * from foo) as foo;
> before it is parsed?

Substituting "planned" for "parsed", yes, pretty nearly. What the
planner sees is practically indistinguishable from what it sees if you
write out the view's definition as a sub-select like that. Exercise for
the student: turn on DEBUG_PRINT_REWRITTEN and compare the post-rewrite
query trees for the two cases. (Also set DEBUG_PRETTY_PRINT to make the
trees dumped into the postmaster log easier to read.)

regards, tom lane