Re: Proposed patch: make SQL interval-literal syntax work per spec

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> I am not sure about some of the corner cases --- anyone want to see if
>> their understanding of the spec for <interval string> is different?

> The patch seems to support extensions to the standard.

Right. All of these were extensions that already existed in PG.

> (3) It seems to me that they were requiring that there be a
> one-to-one match between the fields specified in the interval
> qualifier and the fields present in the interval string.

Yeah. I couldn't actually find any such statement in SQL92, but
the SQL:2008 draft has this at 5.3 rule 30:

30)Let N be the number of <primary datetime field>s in the precision of
the <interval literal>, as specified by <interval qualifier>.

The <interval literal> being defined shall contain N datetime components.

and at rule 34:

34)Within the definition of an <interval literal> that contains a
<year-month literal>, the <interval qualifier> shall not specify DAY,
HOUR, MINUTE, or SECOND. Within the definition of an <interval
literal> that contains a <day-time literal>, the <interval qualifier>
shall not specify YEAR or MONTH.

This seems to be requiring that not only do you give the exact number of
components, but the formatting must match the expectation. So anything
we accept beyond that is gravy. I think that most of the "extension"
cases were already being accepted in some form, and I'd be hesitant
to take them out for fear of breaking existing applications.

>> There is still some unfinished business if anyone wants to make it
>> really exactly 100% spec compliant ...

> I agree.

I committed the patch as presented, and I think I might go take a quick
look at the other two issues. What I suspect I'll find is that the
minus sign issue isn't fixable without turning INTERVAL into a fully
reserved word, which is probably a cure worse than the disease. However
it might be fairly easy to get the grammar to allow the precision in
either place. (We'd want to keep the old way working for backward
compatibility.)

regards, tom lane