PostgreSQL Parse Tree

PostgreSQL接收SQL语句之后，Parser将SQL语句解析成分析树(ParseTree)。

有如下四个表：

create table class(
    classno varchar(20),
    classname varchar(30),
    gno varchar(20)
);

create table student(
    sno varchar(20),
    sname varchar(30),
    sex varchar(5),
    age integer,
    nation varchar(20),
    classno varchar(20)
);

create table course(
    cno varchar(20),
    cname varchar(30),
    credit integer,
    priorcourse varchar(20)
);

create table sc(
    sno varchar(20),
    cno varchar(20),
    score integer
);

在上面四个表上执行如下SQL语句：

select classno,classname,avg(score) as avg_score
from sc,(select * from class where class.gno = '2005') as sub
where sc.sno in (select sno from student where student.classno = sub.classno)
and sc.cno in (select course.cno from course where course.cname = '高等代数')
group by classno, classname
having avg(score) > 80.0
order by avg_score;

经过Parser之后，SQL语句被转换为解析树。对于上面的SQL语句，其对应的解析树如下图所示：

parse tree

SelectStmt

/* ----------------------
 *      Select Statement
 *
 * A "simple" SELECT is represented in the output of gram.y by a single
 * SelectStmt node; so is a VALUES construct.  A query containing set
 * operators (UNION, INTERSECT, EXCEPT) is represented by a tree of SelectStmt
 * nodes, in which the leaf nodes are component SELECTs and the internal nodes
 * represent UNION, INTERSECT, or EXCEPT operators.  Using the same node
 * type for both leaf and internal nodes allows gram.y to stick ORDER BY,
 * LIMIT, etc, clause values into a SELECT statement without worrying
 * whether it is a simple or compound SELECT.
 * ----------------------
 */
typedef enum SetOperation
{
    SETOP_NONE = 0,
    SETOP_UNION,
    SETOP_INTERSECT,
    SETOP_EXCEPT
} SetOperation;

typedef struct SelectStmt
{
    NodeTag     type;

    /*
     * These fields are used only in "leaf" SelectStmts.
     */
    List       *distinctClause; /* NULL, list of DISTINCT ON exprs, or
                                 * lcons(NIL,NIL) for all (SELECT DISTINCT) */
    IntoClause *intoClause;     /* target for SELECT INTO */
    List       *targetList;     /* the target list (of ResTarget) */
    List       *fromClause;     /* the FROM clause */
    Node       *whereClause;    /* WHERE qualification */
    List       *groupClause;    /* GROUP BY clauses */
    Node       *havingClause;   /* HAVING conditional-expression */
    List       *windowClause;   /* WINDOW window_name AS (...), ... */

    /*
     * In a "leaf" node representing a VALUES list, the above fields are all
     * null, and instead this field is set.  Note that the elements of the
     * sublists are just expressions, without ResTarget decoration. Also note
     * that a list element can be DEFAULT (represented as a SetToDefault
     * node), regardless of the context of the VALUES list. It's up to parse
     * analysis to reject that where not valid.
     */
    List       *valuesLists;    /* untransformed list of expression lists */

    /*
     * These fields are used in both "leaf" SelectStmts and upper-level
     * SelectStmts.
     */
    List       *sortClause;     /* sort clause (a list of SortBy's) */
    Node       *limitOffset;    /* # of result tuples to skip */
    Node       *limitCount;     /* # of result tuples to return */
    List       *lockingClause;  /* FOR UPDATE (list of LockingClause's) */
    WithClause *withClause;     /* WITH clause */

    /*
     * These fields are used only in upper-level SelectStmts.
     */
    SetOperation op;            /* type of set op */
    bool        all;            /* ALL specified? */
    struct SelectStmt *larg;    /* left child */
    struct SelectStmt *rarg;    /* right child */
    /* Eventually add fields for CORRESPONDING spec here */
} SelectStmt;