Re: INSERT ... VALUES... with ORDER BY / LIMIT

2010/10/4 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com> writes:
>> DEFAULT is disallowed now in such VALUES list, but we can explain it
>> is allowed in a "simple" VALUES of INSERT case.
>
> I don't think we really need to explain anything.  This is per spec;
> if you trace the way that a DEFAULT expression can appear in INSERT,
> it's treated as a <contextually typed value specification>,
> which appears in <contextually typed table value constructor>,
> which is VALUES and nothing else.

Well, that's great to hear. Reading the spec and our manual, actually
additional clauses to VALUES are all PostgreSQL's extension.

So simply adding these fix it:

/*
* We have three cases to deal with: DEFAULT VALUES (selectStmt == NULL),
- * VALUES list, or general SELECT input. We special-case VALUES, both for
- * efficiency and so we can handle DEFAULT specifications.
+ * simple VALUES list, or general SELECT input including complex VALUES.
+ * We special-case VALUES, both for efficiency and so we can handle
+ * DEFAULT specifications. In a complex VALUES case, which means the list
+ * has any of ORDER BY, OFFSET, LIMIT or WITH, we don't accept DEFAULT
+ * in it; The spec may require it but for now we reject it from point of
+ * code base and expected use cases.
*/
- isGeneralSelect = (selectStmt && selectStmt->valuesLists == NIL);
+ isGeneralSelect = (selectStmt &&
+ (selectStmt->valuesLists == NIL ||
+ selectStmt->sortClause || selectStmt->limitOffset ||
+ selectStmt->limitCount || selectStmt->withClause));

/*
* If a non-nil rangetable/namespace was passed in, and we are doing

I found the current manual of VALUES doesn't mention WITH clause atop
it. Should we add it?

http://www.postgresql.org/docs/9.0/static/sql-values.html

Regards,

--
Hitoshi Harada