Thread: Re: FWD: tinterval vs interval on pgsql-novice
> > Thomas Lockhart would be the authority on this, but my impression is
> > that tinterval is deprecated and will eventually go away in favor of
> > the SQL-standard interval type. If you've found functions that exist
> > for tinterval and not for interval, then that's an item for the TODO
> > list --- please submit details.
> Perhaps I'm not picking up things from the documentation, but it appears to
> me that "interval" is only a time length, while "tinterval" is actually for
> specific times. To use a geometric analogy: interval is a length, while
> tinterval is a specific line segment.
> So it seems to me that interval is just way to generic (or rather,
> tinterval already supports things that I want to do, such as testing for
> overlaps).
TINTERVAL is a poorly supported, old and creaky data type. It is based
on ABSTIME, which is not as capable as TIMESTAMP.
> Am I missing something in the documentation that would explain to me how I
> could use a starttime/length combination (something like abstime/interval,
> or timestamp/interval) to check for overlaps like can be done with tinterval?
Maybe. The SQL9x function/operator OVERLAPS is recognized by PostgreSQL
7.x, and probably does what you want.
Of course, now that I'm testing it, something has broken with OVERLAPS
(in 7.0.3 and current sources). I've defined a function overlaps() which
takes four arguments of timestamp type. The parser recognizes the
OVERLAPS syntax, and converts that to a function call syntax. I've also
defined a few more functions in pg_proc.h in the "SQL language" to map
variations of arguments, say (timestamp,interval,timestamp,interval), to
the underlying single implementation. Pretty sure that I tested this
exhaustively (?). That mapping now fails (hmm, remind me to add this to
the regression tests) with a parser error.
Test cases would be:
select ('today', 'tomorrow') OVERLAPS ('yesterday', 'now');
and
select ('today', interval '1 day') OVERLAPS ('yesterday', interval '18
hours');
(the second one fails). Now that I look, this breakage was introduced in
March when "we" expunged operators allowed as identifiers (Tom Lane and
I have blood on our hands on this one ;) See gram.y around line 5409.
Suggestions?
- Thomas
> (the second one fails). Now that I look, this breakage was introduced in
> March when "we" expunged operators allowed as identifiers (Tom Lane and
> I have blood on our hands on this one ;) See gram.y around line 5409.
> Suggestions?
Any problems with allowing OVERLAPS and BETWEEN as function names? bison
seems happy with no shift/reduce conflicts...
- Thomas
Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
> select ('today', interval '1 day') OVERLAPS ('yesterday', interval '18
> hours');
> (the second one fails). Now that I look, this breakage was introduced in
> March when "we" expunged operators allowed as identifiers (Tom Lane and
> I have blood on our hands on this one ;) See gram.y around line 5409.
I see it does fail, but I'm at a complete loss to understand why,
especially given that the first case still works. The grammar looks
perfectly fine AFAICT. Can you explain what's wrong here?
regards, tom lane
> Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
> > select ('today', interval '1 day') OVERLAPS ('yesterday', interval '18
> > hours');
> > (the second one fails). Now that I look, this breakage was introduced in
> > March when "we" expunged operators allowed as identifiers (Tom Lane and
> > I have blood on our hands on this one ;) See gram.y around line 5409.
> I see it does fail, but I'm at a complete loss to understand why,
> especially given that the first case still works. The grammar looks
> perfectly fine AFAICT. Can you explain what's wrong here?
Yes. There is one underlying routine implementing the OVERLAPS operator.
As you might expect, it is called overlaps() in the catalog, has an
entry point of overlaps_timestamp(), and takes four arguments of type
timestamp. The other variants which accept an interval type for the
second and/or fourth arguments are defined in pg_proc.h as SQL
procedures which simply add, say, the first and second arguments to end
up with four timestamp arguments.
The SQL routine explicitly calls overlaps() as a function, which is
currently disallowed.
Here is what I'm planning on doing (already tested, but not committed).
I'm adding some productions to the func_name rule in gram.y to handle
the various "stringy operators" such as LIKE and OVERLAPS. These tokens
will also be allowed in the ColLabel rule (as several are already).
This fixes the immediate problem, and makes LIKE handling more
consistant with other special functions. Comments?
- Thomas
Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
>> I see it does fail, but I'm at a complete loss to understand why,
>> especially given that the first case still works. The grammar looks
>> perfectly fine AFAICT. Can you explain what's wrong here?
> Here is what I'm planning on doing (already tested, but not committed).
> I'm adding some productions to the func_name rule in gram.y to handle
> the various "stringy operators" such as LIKE and OVERLAPS. These tokens
> will also be allowed in the ColLabel rule (as several are already).
> This fixes the immediate problem, and makes LIKE handling more
> consistant with other special functions. Comments?
That all sounds fine, but it doesn't seem to fix the problem I'm looking
at, which is that the OVERLAPS production is broken in current sources:
template1=# select ('today', 'tomorrow') OVERLAPS ('yesterday', 'now');overlaps
----------t
(1 row)
template1=# select ('today', interval '1 day') OVERLAPS ('yesterday', interval
'18 hours');
ERROR: parser: parse error at or near "overlaps"
I don't understand why we're getting a parse error here ...
regards, tom lane
Tom Lane wrote:
>
> Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
> >> I see it does fail, but I'm at a complete loss to understand why,
> >> especially given that the first case still works. The grammar looks
> >> perfectly fine AFAICT. Can you explain what's wrong here?
>
> > Here is what I'm planning on doing (already tested, but not committed).
> > I'm adding some productions to the func_name rule in gram.y to handle
> > the various "stringy operators" such as LIKE and OVERLAPS. These tokens
> > will also be allowed in the ColLabel rule (as several are already).
> > This fixes the immediate problem, and makes LIKE handling more
> > consistant with other special functions. Comments?
> That all sounds fine, but it doesn't seem to fix the problem I'm looking
> at, which is that the OVERLAPS production is broken in current sources:
Yes it does. When you execute
select (timestamp 'today', interval '1 day') OVERLAPS (timestamp 'yesterday', timestamp 'tomorrow');
This is matched up with an entry in pg_proc which declares an SQL
language implementation as
'select overlaps($1, ($1+$2), $3, $4)'
which is what fails.
It may be better to declare this as
'select ($1, ($1+$2)) overlaps ($3, $4)'
but that is not what is there now. I've just tested the latter form and
it seems to work, so I'll include that in my next patchball.
- Thomas
Tom Lane writes:
> template1=# select ('today', interval '1 day') OVERLAPS ('yesterday', interval
> '18 hours');
> ERROR: parser: parse error at or near "overlaps"
>
> I don't understand why we're getting a parse error here ...
The OVERLAPS special SQL-construct is converted into the 'select
overlaps(...)' function call, which isn't allowed because OVERLAPS is a
keyword. *That* is where the parse error is coming from.
To fix this you simply need to double-quote "overlaps" when it's used as a
straight function call. See how substring does it in pg_proc.h.
--
Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
> To fix this you simply need to double-quote "overlaps" when it's used as a
> straight function call. See how substring does it in pg_proc.h.
Hmm. Why was this required for the substring() example? afaik all of
this should be handled (correctly) in the grammar...
- Thomas
> > To fix this you simply need to double-quote "overlaps" when it's used as a
> > straight function call. See how substring does it in pg_proc.h.
> Hmm. Why was this required for the substring() example? afaik all of
> this should be handled (correctly) in the grammar...
I see it now. Will look at it...
- Thomas