Index: src/backend/nodes/copyfuncs.c =================================================================== RCS file: /home/stark/src/REPOSITORY/pgsql/src/backend/nodes/copyfuncs.c,v retrieving revision 1.360 diff -c -r1.360 copyfuncs.c *** src/backend/nodes/copyfuncs.c 9 Jan 2007 02:14:11 -0000 1.360 --- src/backend/nodes/copyfuncs.c 30 Jan 2007 17:35:15 -0000 *************** *** 1808,1813 **** --- 1808,1814 ---- COPY_NODE_FIELD(limitOffset); COPY_NODE_FIELD(limitCount); COPY_NODE_FIELD(lockingClause); + COPY_NODE_FIELD(with_cte_list); COPY_SCALAR_FIELD(op); COPY_SCALAR_FIELD(all); COPY_NODE_FIELD(larg); Index: src/backend/nodes/equalfuncs.c =================================================================== RCS file: /home/stark/src/REPOSITORY/pgsql/src/backend/nodes/equalfuncs.c,v retrieving revision 1.294 diff -c -r1.294 equalfuncs.c *** src/backend/nodes/equalfuncs.c 9 Jan 2007 02:14:12 -0000 1.294 --- src/backend/nodes/equalfuncs.c 30 Jan 2007 17:35:31 -0000 *************** *** 746,751 **** --- 746,752 ---- COMPARE_NODE_FIELD(limitOffset); COMPARE_NODE_FIELD(limitCount); COMPARE_NODE_FIELD(lockingClause); + COMPARE_NODE_FIELD(with_cte_list); COMPARE_SCALAR_FIELD(op); COMPARE_SCALAR_FIELD(all); COMPARE_NODE_FIELD(larg); Index: src/backend/nodes/outfuncs.c =================================================================== RCS file: /home/stark/src/REPOSITORY/pgsql/src/backend/nodes/outfuncs.c,v retrieving revision 1.292 diff -c -r1.292 outfuncs.c *** src/backend/nodes/outfuncs.c 9 Jan 2007 02:14:12 -0000 1.292 --- src/backend/nodes/outfuncs.c 30 Jan 2007 17:36:05 -0000 *************** *** 1419,1424 **** --- 1419,1425 ---- WRITE_NODE_FIELD(limitOffset); WRITE_NODE_FIELD(limitCount); WRITE_NODE_FIELD(lockingClause); + WRITE_NODE_FIELD(with_cte_list); WRITE_ENUM_FIELD(op, SetOperation); WRITE_BOOL_FIELD(all); WRITE_NODE_FIELD(larg); Index: src/backend/parser/analyze.c =================================================================== RCS file: /home/stark/src/REPOSITORY/pgsql/src/backend/parser/analyze.c,v retrieving revision 1.355 diff -c -r1.355 analyze.c *** src/backend/parser/analyze.c 9 Jan 2007 02:14:13 -0000 1.355 --- src/backend/parser/analyze.c 30 Jan 2007 16:03:59 -0000 *************** *** 2097,2102 **** --- 2097,2105 ---- /* make FOR UPDATE/FOR SHARE info available to addRangeTableEntry */ pstate->p_locking_clause = stmt->lockingClause; + /* process the WITH clause (pull ctes into the pstate's ctenamespace) */ + transformWithClause(pstate, stmt->with_cte_list); + /* process the FROM clause */ transformFromClause(pstate, stmt->fromClause); Index: src/backend/parser/gram.y =================================================================== RCS file: /home/stark/src/REPOSITORY/pgsql/src/backend/parser/gram.y,v retrieving revision 2.573 diff -c -r2.573 gram.y *** src/backend/parser/gram.y 9 Jan 2007 02:14:14 -0000 2.573 --- src/backend/parser/gram.y 30 Jan 2007 17:45:46 -0000 *************** *** 102,108 **** static SelectStmt *findLeftmostSelect(SelectStmt *node); static void insertSelectOptions(SelectStmt *stmt, List *sortClause, List *lockingClause, ! Node *limitOffset, Node *limitCount); static Node *makeSetOp(SetOperation op, bool all, Node *larg, Node *rarg); static Node *doNegate(Node *n, int location); static void doNegateFloat(Value *v); --- 102,109 ---- static SelectStmt *findLeftmostSelect(SelectStmt *node); static void insertSelectOptions(SelectStmt *stmt, List *sortClause, List *lockingClause, ! Node *limitOffset, Node *limitCount, ! List *with_cte_list); static Node *makeSetOp(SetOperation op, bool all, Node *larg, Node *rarg); static Node *doNegate(Node *n, int location); static void doNegateFloat(Value *v); *************** *** 350,355 **** --- 351,360 ---- %type xml_root_version opt_xml_root_standalone %type document_or_content xml_whitespace_option + %type common_table_expression + %type with_cte_list cte_list + %type recursive_is_depth_first + /* * If you make any token changes, update the keyword table in *************** *** 364,370 **** ASSERTION ASSIGNMENT ASYMMETRIC AT AUTHORIZATION BACKWARD BEFORE BEGIN_P BETWEEN BIGINT BINARY BIT ! BOOLEAN_P BOTH BY CACHE CALLED CASCADE CASCADED CASE CAST CHAIN CHAR_P CHARACTER CHARACTERISTICS CHECK CHECKPOINT CLASS CLOSE --- 369,375 ---- ASSERTION ASSIGNMENT ASYMMETRIC AT AUTHORIZATION BACKWARD BEFORE BEGIN_P BETWEEN BIGINT BINARY BIT ! BOOLEAN_P BOTH BREADTH BY CACHE CALLED CASCADE CASCADED CASE CAST CHAIN CHAR_P CHARACTER CHARACTERISTICS CHECK CHECKPOINT CLASS CLOSE *************** *** 376,382 **** DATABASE DAY_P DEALLOCATE DEC DECIMAL_P DECLARE DEFAULT DEFAULTS DEFERRABLE DEFERRED DEFINER DELETE_P DELIMITER DELIMITERS ! DESC DISABLE_P DISTINCT DO DOCUMENT_P DOMAIN_P DOUBLE_P DROP EACH ELSE ENABLE_P ENCODING ENCRYPTED END_P ESCAPE EXCEPT EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN EXTERNAL EXTRACT --- 381,387 ---- DATABASE DAY_P DEALLOCATE DEC DECIMAL_P DECLARE DEFAULT DEFAULTS DEFERRABLE DEFERRED DEFINER DELETE_P DELIMITER DELIMITERS ! DEPTH DESC DISABLE_P DISTINCT DO DOCUMENT_P DOMAIN_P DOUBLE_P DROP EACH ELSE ENABLE_P ENCODING ENCRYPTED END_P ESCAPE EXCEPT EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN EXTERNAL EXTRACT *************** *** 416,426 **** QUOTE ! READ REAL REASSIGN RECHECK REFERENCES REINDEX RELATIVE_P RELEASE RENAME REPEATABLE REPLACE RESET RESTART RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROW ROWS RULE ! SAVEPOINT SCHEMA SCROLL SECOND_P SECURITY SELECT SEQUENCE SERIALIZABLE SESSION SESSION_USER SET SETOF SHARE SHOW SIMILAR SIMPLE SMALLINT SOME STABLE STANDALONE_P START STATEMENT STATISTICS STDIN STDOUT STORAGE STRICT_P STRIP_P SUBSTRING SUPERUSER_P --- 421,431 ---- QUOTE ! READ REAL REASSIGN RECHECK RECURSIVE REFERENCES REINDEX RELATIVE_P RELEASE RENAME REPEATABLE REPLACE RESET RESTART RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROW ROWS RULE ! SAVEPOINT SCHEMA SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE SERIALIZABLE SESSION SESSION_USER SET SETOF SHARE SHOW SIMILAR SIMPLE SMALLINT SOME STABLE STANDALONE_P START STATEMENT STATISTICS STDIN STDOUT STORAGE STRICT_P STRIP_P SUBSTRING SUPERUSER_P *************** *** 5666,5686 **** | select_clause sort_clause { insertSelectOptions((SelectStmt *) $1, $2, NIL, ! NULL, NULL); $$ = $1; } | select_clause opt_sort_clause for_locking_clause opt_select_limit { insertSelectOptions((SelectStmt *) $1, $2, $3, ! list_nth($4, 0), list_nth($4, 1)); $$ = $1; } | select_clause opt_sort_clause select_limit opt_for_locking_clause { insertSelectOptions((SelectStmt *) $1, $2, $4, ! list_nth($3, 0), list_nth($3, 1)); $$ = $1; } ; select_clause: --- 5671,5722 ---- | select_clause sort_clause { insertSelectOptions((SelectStmt *) $1, $2, NIL, ! NULL, NULL, NIL); $$ = $1; } | select_clause opt_sort_clause for_locking_clause opt_select_limit { insertSelectOptions((SelectStmt *) $1, $2, $3, ! list_nth($4, 0), list_nth($4, 1), ! NIL); $$ = $1; } | select_clause opt_sort_clause select_limit opt_for_locking_clause { insertSelectOptions((SelectStmt *) $1, $2, $4, ! list_nth($3, 0), list_nth($3, 1), ! NIL); $$ = $1; } + | with_cte_list simple_select + { + insertSelectOptions((SelectStmt *) $2, + NULL, NIL, + NULL, NULL, + $1); + $$ = $2; + } + | with_cte_list select_clause sort_clause + { + insertSelectOptions((SelectStmt *) $2, $3, NIL, + NULL, NULL, + $1); + $$ = $2; + } + | with_cte_list select_clause opt_sort_clause for_locking_clause opt_select_limit + { + insertSelectOptions((SelectStmt *) $2, $3, $4, + list_nth($5, 0), list_nth($5, 1), + $1); + $$ = $2; + } + | with_cte_list select_clause opt_sort_clause select_limit opt_for_locking_clause + { + insertSelectOptions((SelectStmt *) $2, $3, $5, + list_nth($4, 0), list_nth($4, 1), + $1); + $$ = $2; + } ; select_clause: *************** *** 5742,5747 **** --- 5778,5847 ---- } ; + /* + * ANSI standard WITH clause looks like: + * WITH [ RECURSIVE ] [ (,...) ] AS (query) [SEARCH or CYCLE clause] + * + * It seems "with_cte_list" has to be a separate token or else there's a s/r + * conflict between RECURSIVE and the cte name. This means we'll need a silly + * node just to hold the list and the recursive flag :( it doesn't seem like + * making RECURSIVE a fully reserved word would be very nice as it's probably a + * common column name. + * + * For now we don't support recursive so just ignore it and pass up the list + * + */ + with_cte_list: + WITH cte_list + { + $$ = $2; + } + | WITH RECURSIVE cte_list + { + elog(WARNING, "WITH RECURSIVE not supported yet"); + $$ = $3; + } + ; + + cte_list: + common_table_expression { $$ = list_make1($1); } + | cte_list ',' common_table_expression { $$ = lappend($1, $3); } + ; + + common_table_expression: name opt_name_list AS select_with_parens recursive_search_or_cycle_clause + { + RangeSubselect *n = makeNode(RangeSubselect); + Alias *a = makeNode(Alias); + + n->subquery = $4; + n->alias = a; + n->alias->aliasname = $1; + n->alias->colnames = $2; + + $$ = (Node *) n; + } + ; + + recursive_search_or_cycle_clause: + recursive_search_clause + | recursive_cycle_clause + | recursive_search_clause recursive_cycle_clause + | /* EMPTY */ + ; + + recursive_search_clause: + SEARCH recursive_is_depth_first FIRST_P BY sortby_list SET ColId + ; + + recursive_is_depth_first: + DEPTH {$$ = TRUE;} + | BREADTH {$$ = FALSE;} + ; + + recursive_cycle_clause: + CYCLE columnList SET ColId TO a_expr DEFAULT a_expr USING ColId + ; + into_clause: INTO OptTempTableName { $$ = $2; } | /*EMPTY*/ { $$ = NULL; } *************** *** 8524,8529 **** --- 8624,8630 ---- | BACKWARD | BEFORE | BEGIN_P + | BREADTH | BY | CACHE | CALLED *************** *** 8560,8565 **** --- 8661,8667 ---- | DELETE_P | DELIMITER | DELIMITERS + | DEPTH | DISABLE_P | DOCUMENT_P | DOMAIN_P *************** *** 8669,8684 **** | ROLE | ROLLBACK | ROWS | RULE | SAVEPOINT | SCHEMA | SCROLL | SECOND_P | SECURITY | SEQUENCE | SERIALIZABLE | SESSION - | SET | SHARE | SHOW | SIMPLE --- 8771,8787 ---- | ROLE | ROLLBACK | ROWS + | RECURSIVE | RULE | SAVEPOINT | SCHEMA | SCROLL + | SEARCH | SECOND_P | SECURITY | SEQUENCE | SERIALIZABLE | SESSION | SHARE | SHOW | SIMPLE *************** *** 8719,8725 **** | VIEW | VOLATILE | WHITESPACE_P - | WITH | WITHOUT | WORK | WRITE --- 8822,8827 ---- *************** *** 8888,8893 **** --- 8990,8996 ---- | RETURNING | SELECT | SESSION_USER + | SET | SOME | SYMMETRIC | TABLE *************** *** 8901,8906 **** --- 9004,9010 ---- | USING | WHEN | WHERE + | WITH ; *************** *** 9167,9174 **** static void insertSelectOptions(SelectStmt *stmt, List *sortClause, List *lockingClause, ! Node *limitOffset, Node *limitCount) { /* * Tests here are to reject constructs like * (SELECT foo ORDER BY bar) ORDER BY baz --- 9271,9281 ---- static void insertSelectOptions(SelectStmt *stmt, List *sortClause, List *lockingClause, ! Node *limitOffset, Node *limitCount, ! List *with_cte_list) { + Assert(IsA(stmt, SelectStmt)); + /* * Tests here are to reject constructs like * (SELECT foo ORDER BY bar) ORDER BY baz *************** *** 9199,9204 **** --- 9306,9319 ---- errmsg("multiple LIMIT clauses not allowed"))); stmt->limitCount = limitCount; } + if (with_cte_list) + { + if (stmt->with_cte_list) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("multiple WITH clauses not allowed"))); + stmt->with_cte_list = with_cte_list; + } } static Node * Index: src/backend/parser/keywords.c =================================================================== RCS file: /home/stark/src/REPOSITORY/pgsql/src/backend/parser/keywords.c,v retrieving revision 1.181 diff -c -r1.181 keywords.c *** src/backend/parser/keywords.c 9 Jan 2007 02:14:14 -0000 1.181 --- src/backend/parser/keywords.c 26 Jan 2007 16:29:29 -0000 *************** *** 63,68 **** --- 63,69 ---- {"bit", BIT}, {"boolean", BOOLEAN_P}, {"both", BOTH}, + {"breadth", BREADTH}, {"by", BY}, {"cache", CACHE}, {"called", CALLED}, *************** *** 120,125 **** --- 121,127 ---- {"delete", DELETE_P}, {"delimiter", DELIMITER}, {"delimiters", DELIMITERS}, + {"depth", DEPTH}, {"desc", DESC}, {"disable", DISABLE_P}, {"distinct", DISTINCT}, *************** *** 279,284 **** --- 281,287 ---- {"read", READ}, {"real", REAL}, {"reassign", REASSIGN}, + {"recursive", RECURSIVE}, {"recheck", RECHECK}, {"references", REFERENCES}, {"reindex", REINDEX}, *************** *** 302,307 **** --- 305,311 ---- {"savepoint", SAVEPOINT}, {"schema", SCHEMA}, {"scroll", SCROLL}, + {"search", SEARCH}, {"second", SECOND_P}, {"security", SECURITY}, {"select", SELECT}, Index: src/backend/parser/parse_clause.c =================================================================== RCS file: /home/stark/src/REPOSITORY/pgsql/src/backend/parser/parse_clause.c,v retrieving revision 1.162 diff -c -r1.162 parse_clause.c *** src/backend/parser/parse_clause.c 9 Jan 2007 02:14:14 -0000 1.162 --- src/backend/parser/parse_clause.c 30 Jan 2007 18:16:08 -0000 *************** *** 68,73 **** --- 68,120 ---- /* + * transformWithClause - + * Process the list of WITH clause "common table expressions" into Query nodes. + * + * We just replace the + + + * We need to add the name of the common table expression to a list that is + * used later to find them. But we do _not_ add the table itself to the current + * namespace because that would implicitly join all of them which isn't right. + * + */ + + void + transformWithClause(ParseState *pstate, List *with_cte_list) + { + ListCell *cte; + + + foreach(cte, with_cte_list) + { + RangeSubselect *qry = lfirst(cte), *cte; + List *parsetrees = parse_sub_analyze(qry->subquery, pstate); + Query *query; + + /* Same checkes that FROM does on subqueries XXX refactor? */ + if (list_length(parsetrees) != 1) + elog(ERROR, "unexpected parse analysis result for subquery in WITH"); + query = (Query *) linitial(parsetrees); + if (query == NULL || !IsA(query, Query)) + elog(ERROR, "unexpected parse analysis result for subquery in WITH"); + + if (query->commandType != CMD_SELECT) + elog(ERROR, "expected SELECT query from subquery in WITH"); + if (query->into != NULL) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("subquery in WITH may not have SELECT INTO"))); + + cte = makeNode(RangeSubselect); + cte->subquery = (Node*) query; + cte->alias = copyObject(qry->alias); + + pstate->p_ctenamespace = lappend(pstate->p_ctenamespace, cte); + } + } + + /* * transformFromClause - * Process the FROM clause and add items to the query's range table, * joinlist, and namespaces. *************** *** 410,415 **** --- 457,511 ---- return rte; } + /* + * transformRangeCTE --- transform a RangeVar which references a common table + * expression (ie, a sub-SELECT defined in a WITH clause) + */ + static RangeTblEntry * + transformRangeCTE(ParseState *pstate, RangeVar *n, RangeSubselect *r) + { + RangeTblEntry *rte; + + /* Unlike transformRangeSubselect we do not have to worry about: + * + * . checking for an alias because the grammar for WITH always gives us an + * alias + * + * . transforming the subquery as transformWithClause has already done that + * and the RangeSubselect contains the query tree, not the raw parse tree + * + * . checking for lateral references since WITH subqueries have their own + * scope. Since they were transformed prior to any range table entries + * being created in our pstate they were all planned with a fresh copy of + * our empty pstate (unless we're in a subquery already of course). + */ + + /* This is a kluge for now. Effectively we're inlining all the WITH clauses + * which isn't what we want to do */ + + + /* One tricky bit. We potentially have two aliases here. The WITH clause + * always specifies a relation alias and may or may not specify column + * aliases. The rangevar also may or may not specify a relation alias and + * may or may not specify column aliases. + */ + + + Alias *a = copyObject(r->alias); + if (n->alias && n->alias->aliasname) + a->aliasname = n->alias->aliasname; + if (n->alias && n->alias->colnames) + a->colnames = n->alias->colnames; + /* + * OK, build an RTE for the subquery. + */ + rte = addRangeTableEntryForSubquery(pstate, (Query*) r->subquery, a, true); + + return rte; + } + + + /* * transformRangeSubselect --- transform a sub-SELECT appearing in FROM *************** *** 597,607 **** if (IsA(n, RangeVar)) { /* Plain relation reference */ RangeTblRef *rtr; ! RangeTblEntry *rte; int rtindex; - rte = transformTableEntry(pstate, (RangeVar *) n); /* assume new rte is at end */ rtindex = list_length(pstate->p_rtable); Assert(rte == rt_fetch(rtindex, pstate->p_rtable)); --- 693,720 ---- if (IsA(n, RangeVar)) { /* Plain relation reference */ + RangeVar *rv = (RangeVar *)n; RangeTblRef *rtr; ! RangeTblEntry *rte = NULL; int rtindex; + ListCell *cte; + + if (pstate->p_ctenamespace && !rv->schemaname) { + /* We have to check if this is a reference to a common table + * expression (ie subquery defined in the WITH clause) */ + foreach(cte, pstate->p_ctenamespace) + { + RangeSubselect *r = (RangeSubselect*) lfirst(cte); + if (!strcmp(rv->relname, r->alias->aliasname)) + { + rte = transformRangeCTE(pstate, rv, r); + break; + } + } + } + if (!rte) + rte = transformTableEntry(pstate, rv); /* assume new rte is at end */ rtindex = list_length(pstate->p_rtable); Assert(rte == rt_fetch(rtindex, pstate->p_rtable)); Index: src/include/nodes/parsenodes.h =================================================================== RCS file: /home/stark/src/REPOSITORY/pgsql/src/include/nodes/parsenodes.h,v retrieving revision 1.338 diff -c -r1.338 parsenodes.h *** src/include/nodes/parsenodes.h 9 Jan 2007 02:14:15 -0000 1.338 --- src/include/nodes/parsenodes.h 30 Jan 2007 18:09:27 -0000 *************** *** 789,794 **** --- 789,795 ---- /* * These fields are used only in upper-level SelectStmts. */ + List *with_cte_list; /* List of Common Table Expressions (ie WITH clause) */ SetOperation op; /* type of set op */ bool all; /* ALL specified? */ struct SelectStmt *larg; /* left child */ Index: src/include/parser/parse_clause.h =================================================================== RCS file: /home/stark/src/REPOSITORY/pgsql/src/include/parser/parse_clause.h,v retrieving revision 1.48 diff -c -r1.48 parse_clause.h *** src/include/parser/parse_clause.h 9 Jan 2007 02:14:15 -0000 1.48 --- src/include/parser/parse_clause.h 30 Jan 2007 16:07:39 -0000 *************** *** 16,21 **** --- 16,22 ---- #include "parser/parse_node.h" + extern void transformWithClause(ParseState *pstate, List *with_cte_list); extern void transformFromClause(ParseState *pstate, List *frmList); extern int setTargetTable(ParseState *pstate, RangeVar *relation, bool inh, bool alsoSource, AclMode requiredPerms); Index: src/include/parser/parse_node.h =================================================================== RCS file: /home/stark/src/REPOSITORY/pgsql/src/include/parser/parse_node.h,v retrieving revision 1.51 diff -c -r1.51 parse_node.h *** src/include/parser/parse_node.h 5 Jan 2007 22:19:57 -0000 1.51 --- src/include/parser/parse_node.h 30 Jan 2007 16:07:01 -0000 *************** *** 58,63 **** --- 58,70 ---- * of ParseStates, only the topmost ParseState contains paramtype info; but * we copy the p_variableparams flag down to the child nodes for speed in * coerce_type. + * + * [1] Note that p_ctenamespace is a namespace for "relations" but distinct + * from p_relnamespace. p_ctenamespace is the list of relations that can be + * referred to in a FROM or JOIN clause. p_relnamespace is the list of + * relations which already have and therefore can be referred to in + * qualified variable references. Also, note that p_ctenamespace is a list + * of RangeSubselects, not a list of range table entries. */ typedef struct ParseState { *************** *** 68,73 **** --- 75,81 ---- * node's fromlist) */ List *p_relnamespace; /* current namespace for relations */ List *p_varnamespace; /* current namespace for columns */ + List *p_ctenamespace; /* current namespace for common table expressions [1] */ Oid *p_paramtypes; /* OIDs of types for $n parameter symbols */ int p_numparams; /* allocated size of p_paramtypes[] */ int p_next_resno; /* next targetlist resno to assign */