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@decibel.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@nasby.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@postgresql.org so that your
>        message can get through to the mailing list cleanly

--  Bruce Momjian  <bruce@momjian.us>          http://momjian.us EnterpriseDB
http://www.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +