Attached is an updated version of Greg Stark's patch to add support for
the non-recursive variant of the SQL99 WITH clause[1]. I haven't looked
at the actual functionality of the patch yet (which is quite trivial) --
I just fixed up bitrot and the like. I also removed support for
RECURSIVE and the search/cycle clause, along with their associated
keywords -- the current patch doesn't approach anything close to adding
support for the non-recursive case, so it seems like a net loss to add
additional keywords for no gain in functionality.
Remaining work is to review the guts of the patch (which shouldn't take
long), and write documentation and regression tests. I'm personally
hoping to see this get into the tree fairly early in the 8.4 cycle,
pending discussion of course.
-Neil
[1] http://archives.postgresql.org/pgsql-patches/2007-03/msg00139.php
http://archives.postgresql.org/pgsql-patches/2007-04/msg00055.php
Index: src/backend/nodes/copyfuncs.c
===================================================================
RCS file: /home/neilc/postgres/cvs_root/pgsql/src/backend/nodes/copyfuncs.c,v
retrieving revision 1.387
diff -p -c -r1.387 copyfuncs.c
*** src/backend/nodes/copyfuncs.c 1 Jan 2008 19:45:50 -0000 1.387
--- src/backend/nodes/copyfuncs.c 27 Jan 2008 07:05:29 -0000
*************** _copySelectStmt(SelectStmt *from)
*** 1930,1935 ****
--- 1930,1936 ----
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/neilc/postgres/cvs_root/pgsql/src/backend/nodes/equalfuncs.c,v
retrieving revision 1.317
diff -p -c -r1.317 equalfuncs.c
*** src/backend/nodes/equalfuncs.c 1 Jan 2008 19:45:50 -0000 1.317
--- src/backend/nodes/equalfuncs.c 27 Jan 2008 07:05:29 -0000
*************** _equalSelectStmt(SelectStmt *a, SelectSt
*** 821,826 ****
--- 821,827 ----
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/neilc/postgres/cvs_root/pgsql/src/backend/nodes/outfuncs.c,v
retrieving revision 1.322
diff -p -c -r1.322 outfuncs.c
*** src/backend/nodes/outfuncs.c 9 Jan 2008 08:46:44 -0000 1.322
--- src/backend/nodes/outfuncs.c 27 Jan 2008 07:05:29 -0000
*************** _outSelectStmt(StringInfo str, SelectStm
*** 1599,1604 ****
--- 1599,1605 ----
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/neilc/postgres/cvs_root/pgsql/src/backend/parser/analyze.c,v
retrieving revision 1.371
diff -p -c -r1.371 analyze.c
*** src/backend/parser/analyze.c 1 Jan 2008 19:45:50 -0000 1.371
--- src/backend/parser/analyze.c 27 Jan 2008 07:23:39 -0000
*************** transformSelectStmt(ParseState *pstate,
*** 688,693 ****
--- 688,696 ----
/* 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/neilc/postgres/cvs_root/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.605
diff -p -c -r2.605 gram.y
*** src/backend/parser/gram.y 1 Jan 2008 19:45:50 -0000 2.605
--- src/backend/parser/gram.y 27 Jan 2008 07:21:28 -0000
*************** static List *extractArgTypes(List *param
*** 103,109 ****
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);
--- 103,110 ----
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);
*************** static Node *makeXmlExpr(XmlExprOp op, c
*** 357,362 ****
--- 358,366 ----
%type <ival> document_or_content
%type <boolean> xml_whitespace_option
+ %type <node> common_table_expression
+ %type <list> with_cte_list cte_list
+
/*
* If you make any token changes, update the keyword table in
*************** select_no_parens:
*** 6160,6180 ****
| 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:
--- 6164,6215 ----
| 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:
*************** simple_select:
*** 6235,6240 ****
--- 6270,6308 ----
}
;
+ /*
+ * ANSI standard WITH clause looks like:
+ *
+ * WITH [ RECURSIVE ] <query name> [ (<column>,...) ]
+ * AS (query) [ SEARCH or CYCLE clause ]
+ *
+ * We don't currently support RECURSIVE, or the SEARCH or CYCLE clause.
+ */
+ with_cte_list:
+ WITH cte_list
+ {
+ $$ = $2;
+ }
+ ;
+
+ 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
+ {
+ RangeSubselect *n = makeNode(RangeSubselect);
+
+ n->subquery = $4;
+ n->alias = makeNode(Alias);
+ n->alias->aliasname = $1;
+ n->alias->colnames = $2;
+
+ $$ = (Node *) n;
+ }
+ ;
+
into_clause:
INTO OptTempTableName
{
*************** unreserved_keyword:
*** 9224,9230 ****
| VIEW
| VOLATILE
| WHITESPACE_P
- | WITH
| WITHOUT
| WORK
| WRITE
--- 9292,9297 ----
*************** reserved_keyword:
*** 9406,9411 ****
--- 9473,9479 ----
| USING
| WHEN
| WHERE
+ | WITH
;
*************** findLeftmostSelect(SelectStmt *node)
*** 9672,9679 ****
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
--- 9740,9750 ----
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
*************** insertSelectOptions(SelectStmt *stmt,
*** 9704,9709 ****
--- 9775,9788 ----
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/parse_clause.c
===================================================================
RCS file: /home/neilc/postgres/cvs_root/pgsql/src/backend/parser/parse_clause.c,v
retrieving revision 1.168
diff -p -c -r1.168 parse_clause.c
*** src/backend/parser/parse_clause.c 1 Jan 2008 19:45:50 -0000 1.168
--- src/backend/parser/parse_clause.c 27 Jan 2008 07:44:19 -0000
*************** static TargetEntry *findTargetlistEntry(
*** 68,73 ****
--- 68,112 ----
/*
+ * transformWithClause -
+ * Transform the list of WITH clause "common table expressions" into
+ * Query nodes.
+ *
+ * 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 *lc;
+
+ foreach(lc, with_cte_list)
+ {
+ RangeSubselect *cte = lfirst(lc);
+ RangeSubselect *new_cte;
+ Query *query;
+
+ query = parse_sub_analyze(cte->subquery, pstate);
+
+ /* Same checks that FROM does on subqueries XXX refactor? */
+ if (query->commandType != CMD_SELECT ||
+ query->utilityStmt != NULL)
+ elog(ERROR, "expected SELECT query from subquery in WITH");
+ if (query->intoClause)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("subquery in WITH cannot have SELECT INTO")));
+
+ new_cte = makeNode(RangeSubselect);
+ new_cte->subquery = (Node*) query;
+ new_cte->alias = copyObject(cte->alias);
+
+ pstate->p_ctenamespace = lappend(pstate->p_ctenamespace, new_cte);
+ }
+ }
+
+ /*
* transformFromClause -
* Process the FROM clause and add items to the query's range table,
* joinlist, and namespaces.
*************** transformTableEntry(ParseState *pstate,
*** 410,415 ****
--- 449,503 ----
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
*************** transformFromClauseItem(ParseState *psta
*** 590,600 ****
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));
--- 678,715 ----
if (IsA(n, RangeVar))
{
/* Plain relation reference */
+ RangeVar *rv = (RangeVar *) n;
RangeTblRef *rtr;
! RangeTblEntry *rte = NULL;
int rtindex;
! if (!rv->schemaname)
! {
! /*
! * We have to check if this is a reference to a common table
! * expression (ie subquery defined in the WITH clause). Either
! * in this query or any parent query.
! */
! ParseState *ps;
! ListCell *lc;
!
! for (ps = pstate; ps; ps = ps->parentParseState)
! {
! foreach(lc, ps->p_ctenamespace)
! {
! RangeSubselect *r = (RangeSubselect *) lfirst(lc);
! if (strcmp(rv->relname, r->alias->aliasname) == 0)
! {
! 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/neilc/postgres/cvs_root/pgsql/src/include/nodes/parsenodes.h,v
retrieving revision 1.358
diff -p -c -r1.358 parsenodes.h
*** src/include/nodes/parsenodes.h 1 Jan 2008 19:45:58 -0000 1.358
--- src/include/nodes/parsenodes.h 27 Jan 2008 07:05:29 -0000
*************** typedef struct SelectStmt
*** 763,768 ****
--- 763,769 ----
/*
* 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/neilc/postgres/cvs_root/pgsql/src/include/parser/parse_clause.h,v
retrieving revision 1.49
diff -p -c -r1.49 parse_clause.h
*** src/include/parser/parse_clause.h 1 Jan 2008 19:45:58 -0000 1.49
--- src/include/parser/parse_clause.h 27 Jan 2008 07:05:29 -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/neilc/postgres/cvs_root/pgsql/src/include/parser/parse_node.h,v
retrieving revision 1.53
diff -p -c -r1.53 parse_node.h
*** src/include/parser/parse_node.h 1 Jan 2008 19:45:58 -0000 1.53
--- src/include/parser/parse_node.h 27 Jan 2008 07:05:29 -0000
***************
*** 58,63 ****
--- 58,71 ----
* 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 a list of relations that can be
+ * referred to in a FROM or JOIN clause (in addition to normal tables and
+ * views). p_relnamespace is the list of relations which already have been
+ * listed in such clauses 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
{
*************** typedef struct ParseState
*** 68,73 ****
--- 76,82 ----
* 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 */
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
