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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On 5/17/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> 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?
>

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On 5/17/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> "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.
>


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:
>
> On 5/17/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >
> > "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.
> >
>
>
> 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.
>

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"

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate