Re: AS OF queries

On Wed, Dec 20, 2017 at 4:26 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com> writes:
> > On 12/20/17 10:29, Tom Lane wrote:
> >> Please say that's just an Oracle-ism and not SQL standard, because it's
> >> formally ambiguous.
>
> > The SQL standard syntax appears to be something like
>
> > "tablename" [ AS OF SYSTEM TIME 'something' ] [ [ AS ] "alias" ]
>
> > That's not going to be fun to parse.
>

Examples from DB2 documentation (which may be closer to the standard):

SELECT coverage_amt
FROM policy FOR SYSTEM_TIME AS OF '2010-12-01'
WHERE id = 1111;

SELECT count(*)
FROM policy FOR SYSTEM_TIME FROM '2011-11-30'
TO '9999-12-30'
WHERE vin = 'A1111';

So besides AS .. AS , it could also be FROM .. FROM

> Bleah. In principle we could look two tokens ahead so as to recognize
> "AS OF SYSTEM", but base_yylex is already a horrid mess with one-token
> lookahead; I don't much want to try to extend it to that.
>
> Possibly the most workable compromise is to use lookahead to convert
> "AS OF" to "AS_LA OF", and then we could either just break using OF
> as an alias, or add an extra production that allows "AS_LA OF" to
> be treated as "AS alias" if it's not followed by the appropriate
> stuff.
>
> It's a shame that the SQL committee appears to be so ignorant of
> standard parsing technology.
>
> regards, tom lane
>
>