Thread: interval / interval -> double operator

Yet another potential addition to the family of operators. Some guy
was asking for it on IRC so...

CREATE OR REPLACE FUNCTION interval_over_interval(interval, interval)
RETURNS float STRICT IMMUTABLE LANGUAGE sql AS $$
SELECT extract(epoch from $1)::float / extract(epoch from $2);
$$;

CREATE OPERATOR /
(   leftarg = interval
,   rightarg = interval
,   procedure = interval_over_interval
);

Andrew Hammond <andrew.george.hammond@gmail.com> writes:
> Yet another potential addition to the family of operators. Some guy
> was asking for it on IRC so...

> CREATE OR REPLACE FUNCTION interval_over_interval(interval, interval)
> RETURNS float STRICT IMMUTABLE LANGUAGE sql AS $$
> SELECT extract(epoch from $1)::float / extract(epoch from $2);
> $$;

What are the grounds for defining it that way rather than some other
way?
        regards, tom lane

On 5/17/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
The only alternative that came to mind when I wrote it was using a numeric instead of float. I couldn't see why a numeric with some arbitrary precision / scale was particularly better than just using a double precision. There's already an interval_div function in the catalog which take an interval and a double precision and returns an interval, so using floating point math already has precedent. I figured that if I went with numeric, I'd also have to have a pretty good reason to change the existing operator or it'd inconsistent. Since float (without parameters) is both shorter to type and appears to be the same as double precision (at least according to the docs), my innate lazy streak went that way.

Am I missing something obvious?

Andrew

"Andrew Hammond" <andrew.george.hammond@gmail.com> writes:
> On 5/17/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> What are the grounds for defining it that way rather than some other
>> way?

> The only alternative that came to mind when I wrote it was using a numeric
> instead of float.

No, I'm wondering what's the justification for smashing it to a single
number at all, when the inputs are three-field values.  Interval divided
by float doesn't produce just a float, for example.
        regards, tom lane

On 5/17/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:

I think I see what you're getting at here. '1 month' / '1 day' could return a number of reasonable values depending on how many days are in the month (28 to 31) and on how many hours are in a day (generally 24, but can be 23 or 25 for DST adjustments). The definition above simply assumes that EXTRACT(epoch...) does the Right Thing. Hmmm. I'm at a loss for the right way to solve this. It seems very reasonable to want to divide intervals by intervals (how many nanocenturies in a fortnight?), but I'm at a loss for how to do that correctly. I'll read the code from EXTRACT(epoch...) and see what happening there.

Andrew

On 5/18/07, Andrew Hammond <andrew.george.hammond@gmail.com> wrote:
Ok, I've been hunting through src/backend to try and find the code for EXTRACT(epoch ...). I found EXTRACT in src/backend/parser/gram.y, which seems like a reasonable place to start.

  | EXTRACT '(' extract_list ')'
      {
          FuncCall *n = makeNode(FuncCall);
          n->funcname = SystemFuncName("date_part");
          n->args = $3;
          n->agg_star = FALSE;
          n->agg_distinct = FALSE;
          n->location = @1;
          $$ = (Node *)n;
     }

Which got me looking for "date_part". But that only seems to be in the gram.y file, include/catalog/pg_proc.h and the test suite. The pg_proc.h stuff looks pretty interesting, but to decipher it, I figured I need to read up on SystemFuncName(). So I grepped for SystemFuncName(). It turns out to be a wrapper around list_make2(), which is part of the linked list package. Then I checked out makeNode(), which is a wrapper around newNode(), which in turn is memory allocation stuff. At this point I'm kind of lost. I'm pretty sure that the next thing I need to hunt up is in the parser, but I don't know where to look.

Can anyone please tell me what is the right way to chase down the actual code that implements EXTRACT(epoch ...)? (please note that I'm not asking where that code is, but how to find it.) Or even better, is there a web page or other document someone can give me a pointer to?

Andrew

Andrew Hammond escribió:

> Ok, I've been hunting through src/backend to try and find the code for
> EXTRACT(epoch ...). I found EXTRACT in src/backend/parser/gram.y, which
> seems like a reasonable place to start.
> 
>  | EXTRACT '(' extract_list ')'
>      {
>          FuncCall *n = makeNode(FuncCall);
>          n->funcname = SystemFuncName("date_part");
>          n->args = $3;
>          n->agg_star = FALSE;
>          n->agg_distinct = FALSE;
>          n->location = @1;
>          $$ = (Node *)n;
>     }
> 
> Which got me looking for "date_part". But that only seems to be in the
> gram.y file, include/catalog/pg_proc.h and the test suite. The pg_proc.h
> stuff looks pretty interesting, but to decipher it, I figured I need to read
> up on SystemFuncName().

That's where you got lost -- if you had looked at the pg_proc.h entries
more carefully you would have seen that they point to other functions,
like timestamptz_part and friends.  You can find them on timestamp.c,
etc.  The key is knowing that the pg_proc.h entry maps from a SQL
function name into a C function name.

-- 
Alvaro Herrera                 http://www.amazon.com/gp/registry/DXLWNGRJD34J
"La naturaleza, tan frágil, tan expuesta a la muerte... y tan viva"

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Andrew Hammond escribi�:
>> Which got me looking for "date_part". But that only seems to be in the
>> gram.y file, include/catalog/pg_proc.h and the test suite. The pg_proc.h
>> stuff looks pretty interesting, but to decipher it, I figured I need to read
>> up on SystemFuncName().

> That's where you got lost -- if you had looked at the pg_proc.h entries
> more carefully you would have seen that they point to other functions,

Yeah --- AFAIR SystemFuncName() is just a convenience function that
does the equivalent of plastering "pg_catalog." on the front of the
mentioned name, so that it can't be confused with any non-built-in
function.
        regards, tom lane