Re: temporal variants of generate_series()

This has been saved for the 8.4 release:

http://momjian.postgresql.org/cgi-bin/pgpatches_hold

---------------------------------------------------------------------------

Jim Nasby wrote:
> On May 6, 2007, at 8:07 PM, Tom Lane wrote:
> > Jim Nasby <decibel(at)decibel(dot)org> writes:
> >> Also, what would be the appropriate way to put this into initdb?
> > You seem to have missed a step here, which is to convince people that
> > these belong in core at all. So far I've not even seen an argument
> > that
> > would justify putting them in contrib.
>
> These are all examples of using generate series plus additional math
> to generate a series of dates/timestamps:
> http://archives.postgresql.org/pgsql-general/2007-01/msg01292.php
> http://archives.postgresql.org/pgsql-sql/2006-02/msg00249.php
> http://archives.postgresql.org/pgsql-general/2005-06/msg01254.php
> http://archives.postgresql.org/pgsql-sql/2007-03/msg00093.php
> http://archives.postgresql.org/pgsql-novice/2007-01/msg00002.php
> http://archives.postgresql.org/pgsql-sql/2006-03/msg00391.php
> http://archives.postgresql.org/pgsql-hackers/2006-09/msg00330.php
>
> That's from the first page of search results for 'generate_series
> timestamp'.
>
> FWIW, I could also make use of this in some of my code.
>
> > If they *were* of sufficiently
> > wide use to justify putting them into core, a more efficient
> > implementation would probably be expected.
>
> Ok, I'll look into a C version, but why do SQL functions have such a
> high overhead? I'm seeing an SQL function taking ~2.6x longer than
> the equivalent code run directly in a query. With 100 days, the
> difference drops a bit to ~2.4x. (this is on HEAD from a few months ago)
>
> This is on my MacBook Pro with the Jean-Pierre's version of
> generate_series:
>
> decibel=# select count(*) from generate_series(now(),now()+'10
> days'::interval,'1'::interval);
> Time: 1851.407 ms
> decibel=# select count(*) from generate_series(1,86400*10);
> Time: 657.894 ms
> decibel=# select count(*) from (select now() + (generate_series
> (1,86400*10) * '1 second'::interval)) a;
> Time: 733.592 ms
> decibel=# select count(*) from (select 'epoch'::timestamptz + s.i *
> '1 second'::interval AS "generate_series" from generate_series(extract
> ('epoch' from now())::bigint, extract('epoch' from now()+'10
> days'::interval)::bigint, extract('epoch' from
> '1'::interval)::bigint) s(i)) a;
> Time: 699.606 ms
> --
> Jim Nasby jim(at)nasby(dot)net
> EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +