Re: Parsing of VIEW definitions - Mailing list pgsql-general

"Eric B.Ridge" <ebr@tcdi.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
:hasAggsfalse :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
:checkForReadtrue :checkForWrite false :checkAsUser 1}) :jointree {FROMEXPR :fromlist ({RANGETBLREF :rtindex 3}) :quals
<>}:rowMarks () :targetList ({TARGETENTRY :resdom {RESDOM :resno 1 :restype 23 :restypmod -1 :resname f1
:ressortgroupref0 :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