Re: [HACKERS] temporal variants of generate_series()

2008-03-11 Thread Bruce Momjian 

Added to TODO:

* Add temporal versions of generate_series()

  http://archives.postgresql.org/pgsql-hackers/2007-04/msg01180.php



---

Jim Nasby wrote:
 On May 6, 2007, at 8:07 PM, Tom Lane wrote:
  Jim Nasby [EMAIL PROTECTED] 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/msg2.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[EMAIL PROTECTED]
 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 [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

-- 
  Bruce Momjian  [EMAIL PROTECTED]http://momjian.us
  EnterpriseDB http://postgres.enterprisedb.com

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] temporal variants of generate_series()

2007-05-16 Thread Bruce Momjian 

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 [EMAIL PROTECTED] 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/msg2.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[EMAIL PROTECTED]
 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 [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

-- 
  Bruce Momjian  [EMAIL PROTECTED]  http://momjian.us
  EnterpriseDB   http://www.enterprisedb.com

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

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

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

Re: [HACKERS] temporal variants of generate_series()

2007-05-07 Thread Jim Nasby 

On May 6, 2007, at 8:07 PM, Tom Lane wrote:

Jim Nasby [EMAIL PROTECTED] 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/msg2.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[EMAIL PROTECTED]
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 [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [HACKERS] temporal variants of generate_series()

2007-05-06 Thread Tom Lane 
Jim Nasby [EMAIL PROTECTED] 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.  If they *were* of sufficiently
wide use to justify putting them into core, a more efficient
implementation would probably be expected.

regards, tom lane

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [HACKERS] temporal variants of generate_series()

2007-05-05 Thread Jim Nasby 

On May 2, 2007, at 8:24 PM, JEAN-PIERRE PELLETIER wrote:
On the date variant, I wasn't sure how to handle intervals with  
parts smaller than days:

floor, ceiling, round or error out


Hrm... I'm not sure what would be better there... I'm leaning towards  
round (floor or ceil don't make much sense to me), but I could also  
see throwing an error if trunc('day', $3) != $3. Comments?


Also, what would be the appropriate way to put this into initdb?  
These seem a bit long to try and cram into a one-line DATA statement  
in pg_proc.h. Should I add a new .sql file ala  
information_schema.sql? Is it possible to still add pg_catalog  
entries after the postgresql.bki stage of initdb?


Finally, should I also add a timestamp without time zone version? I  
know we'll automatically cast timestamptz to timestamp, but then you  
get a timestamptz back, which seems odd.



To get round, the last parameters of generate_series would be
extract('epoch' FROM '1 day'::interval)::bigint * round(extract 
('epoch' FROM $3) / extract('epoch' FROM '1 day'::interval))::bigint


CREATE OR REPLACE FUNCTION generate_series (
   start_ts timestamptz,
   end_ts timestamptz,
   step interval
) RETURNS SETOF timestamptz
STRICT
LANGUAGE sql
AS $$
SELECT
  'epoch'::timestamptz + s.i * '1 second'::interval AS  
generate_series

FROM
  generate_series(
   extract('epoch' FROM $1)::bigint,
   extract('epoch' FROM $2)::bigint,
   extract('epoch' FROM $3)::bigint
  ) s(i);
$$;

CREATE OR REPLACE FUNCTION generate_series (
   start_ts date,
   end_ts date,
   step interval
) RETURNS SETOF date
STRICT
LANGUAGE sql
AS $$
SELECT
  ('epoch'::date + s.i * '1 second'::interval)::date AS  
generate_series

FROM
  generate_series(
   extract('epoch' FROM $1)::bigint,
   extract('epoch' FROM $2)::bigint,
   extract('epoch' FROM date_trunc('day', $3))::bigint -- does  
a floor

  ) s(i);
$$;

Jean-Pierre Pelletier
e-djuster



---(end of  
broadcast)---

TIP 7: You can help support the PostgreSQL project by donating at

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



--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] temporal variants of generate_series()

2007-05-02 Thread JEAN-PIERRE PELLETIER 

Here's a shorter version:

On the date variant, I wasn't sure how to handle intervals with parts 
smaller than days:

floor, ceiling, round or error out
To get round, the last parameters of generate_series would be
extract('epoch' FROM '1 day'::interval)::bigint * round(extract('epoch' FROM 
$3) / extract('epoch' FROM '1 day'::interval))::bigint


CREATE OR REPLACE FUNCTION generate_series (
   start_ts timestamptz,
   end_ts timestamptz,
   step interval
) RETURNS SETOF timestamptz
STRICT
LANGUAGE sql
AS $$
SELECT
  'epoch'::timestamptz + s.i * '1 second'::interval AS generate_series
FROM
  generate_series(
   extract('epoch' FROM $1)::bigint,
   extract('epoch' FROM $2)::bigint,
   extract('epoch' FROM $3)::bigint
  ) s(i);
$$;

CREATE OR REPLACE FUNCTION generate_series (
   start_ts date,
   end_ts date,
   step interval
) RETURNS SETOF date
STRICT
LANGUAGE sql
AS $$
SELECT
  ('epoch'::date + s.i * '1 second'::interval)::date AS generate_series
FROM
  generate_series(
   extract('epoch' FROM $1)::bigint,
   extract('epoch' FROM $2)::bigint,
   extract('epoch' FROM date_trunc('day', $3))::bigint -- does a floor
  ) s(i);
$$;

Jean-Pierre Pelletier
e-djuster



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

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

Re: [HACKERS] temporal variants of generate_series()

2007-05-02 Thread JEAN-PIERRE PELLETIER 

Here's a shorter version:

On the date variant, I wasn't sure how to handle intervals with parts 
smaller than days:

floor, ceiling, round or error out
To get round, the last parameters of generate_series would be
extract('epoch' FROM '1 day'::interval)::bigint * round(extract('epoch' FROM 
$3) / extract('epoch' FROM '1 day'::interval))::bigint


CREATE OR REPLACE FUNCTION generate_series (
   start_ts timestamptz,
   end_ts timestamptz,
   step interval
) RETURNS SETOF timestamptz
STRICT
LANGUAGE sql
AS $$
SELECT
  'epoch'::timestamptz + s.i * '1 second'::interval AS generate_series
FROM
  generate_series(
   extract('epoch' FROM $1)::bigint,
   extract('epoch' FROM $2)::bigint,
   extract('epoch' FROM $3)::bigint
  ) s(i);
$$;

CREATE OR REPLACE FUNCTION generate_series (
   start_ts date,
   end_ts date,
   step interval
) RETURNS SETOF date
STRICT
LANGUAGE sql
AS $$
SELECT
  ('epoch'::date + s.i * '1 second'::interval)::date AS generate_series
FROM
  generate_series(
   extract('epoch' FROM $1)::bigint,
   extract('epoch' FROM $2)::bigint,
   extract('epoch' FROM date_trunc('day', $3))::bigint -- does a floor
  ) s(i);
$$;

Jean-Pierre Pelletier
e-djuster



---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [HACKERS] temporal variants of generate_series()

2007-05-01 Thread Jim Nasby 

On Apr 28, 2007, at 8:00 PM, David Fetter wrote:

Here's an SQL version without much in the way of bounds checking :)

CREATE OR REPLACE FUNCTION generate_series (
start_ts timestamptz,
end_ts timestamptz,
step interval
) RETURNS SETOF timestamptz
LANGUAGE sql
AS $$
SELECT
CASE
WHEN $1  $2 THEN
$1
WHEN $1  $2 THEN
$2
END + s.i * $3 AS generate_series
FROM generate_series(
0,
floor(
CASE
WHEN $1  $2 AND $3  INTERVAL '0 seconds' THEN
extract('epoch' FROM $2) -
extract('epoch' FROM $1)
WHEN $1  $2 AND $3  INTERVAL '0 seconds' THEN
extract('epoch' FROM $1) -
extract('epoch' FROM $2)
END/extract('epoch' FROM $3)
)::int8
) AS s(i);
$$;

It should be straight-forward to make similar ones to those below.


Are you sure the case statements are needed? It seems it would be  
better to just punt to the behavior of generate_series (esp. if  
generate_series eventually learns how to count backwards).

--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)



---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [HACKERS] temporal variants of generate_series()

2007-05-01 Thread Tom Lane 
Jim Nasby [EMAIL PROTECTED] writes:
 Are you sure the case statements are needed? It seems it would be  
 better to just punt to the behavior of generate_series (esp. if  
 generate_series eventually learns how to count backwards).

What's this eventually?

regression=# select * from generate_series(10,1,-1);
 generate_series
-
  10
   9
   8
   7
   6
   5
   4
   3
   2
   1
(10 rows)

regards, tom lane

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] temporal variants of generate_series()

2007-05-01 Thread David Fetter 
On Tue, May 01, 2007 at 05:08:45PM -0400, Tom Lane wrote:
 Jim Nasby [EMAIL PROTECTED] writes:
  Are you sure the case statements are needed? It seems it would be  
  better to just punt to the behavior of generate_series (esp. if  
  generate_series eventually learns how to count backwards).
 
 What's this eventually?
 
 regression=# select * from generate_series(10,1,-1);
  generate_series
 -
   10
9
8
7
6
5
4
3
2
1
 (10 rows)
 
   regards, tom lane

Good point.  I believe the function below does the right thing.  When
given decreasing TIMESTAMPTZs and a negative interval, it will
generate them going backward in time.  When given increasing
TIMESTAMPTZs and a positive interval, it will generate them going
forward in time.  Given a 0 interval, it errors out, although not with
the same message as generate_series(1,1,0), and decreasing
TIMESTAMPTZs and a positive interval or vice versa, it generates no
rows.

CREATE OR REPLACE FUNCTION generate_series (
start_ts timestamptz,
end_ts timestamptz,
step interval
) RETURNS SETOF timestamptz
STRICT
LANGUAGE sql
AS $$
SELECT
$1 + s.i * $3 AS generate_series
FROM generate_series(
CASE WHEN $1 = $2
THEN 0
ELSE
floor(
(
extract('epoch' FROM $2) - extract('epoch' FROM $1)
)/extract('epoch' FROM $3)
)::int8
END,
CASE WHEN $1 = $2
THEN ceil(
(
extract('epoch' FROM $2) - extract('epoch' FROM $1)
)/extract('epoch' FROM $3)
)::int8
ELSE
0
END,
sign(
extract('epoch' FROM $2) - extract('epoch' FROM $1)
)::int8
) AS s(i)
ORDER BY s.i ASC
;
$$;

-- 
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 415 235 3778AIM: dfetter666
  Skype: davidfetter

Remember to vote!
Consider donating to PostgreSQL: http://www.postgresql.org/about/donate

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [HACKERS] temporal variants of generate_series()

2007-04-28 Thread David Fetter 
On Thu, Apr 12, 2007 at 02:56:24PM -0700, Andrew Hammond wrote:
 I've written the following function definitions to extend
 generate_series to support some temporal types (timestamptz, date and
 time). Please include them if there's sufficient perceived need or
 value.
 
 -- timestamptz version
 CREATE OR REPLACE FUNCTION generate_series
 ( start_ts timestamptz
 , end_ts timestamptz
 , step interval
 ) RETURNS SETOF timestamptz
 AS $$
 DECLARE
 current_ts timestamptz := start_ts;
 BEGIN
 IF start_ts  end_ts AND step  INTERVAL '0 seconds' THEN
 LOOP
 IF current_ts  end_ts THEN
 RETURN;
 END IF;
 RETURN NEXT current_ts;
 current_ts := current_ts + step;
 END LOOP;
 ELSIF end_ts  start_ts AND step  INTERVAL '0 seconds' THEN
 LOOP
 IF current_ts  end_ts THEN
 RETURN;
 END IF;
 RETURN NEXT current_ts;
 current_ts := current_ts + step;
 END LOOP;
 END IF;
 END;
 $$ LANGUAGE plpgsql IMMUTABLE;

Here's an SQL version without much in the way of bounds checking :)

CREATE OR REPLACE FUNCTION generate_series (
start_ts timestamptz,
end_ts timestamptz,
step interval
) RETURNS SETOF timestamptz
LANGUAGE sql
AS $$
SELECT
CASE
WHEN $1  $2 THEN
$1
WHEN $1  $2 THEN
$2
END + s.i * $3 AS generate_series
FROM generate_series(
0,
floor(
CASE
WHEN $1  $2 AND $3  INTERVAL '0 seconds' THEN
extract('epoch' FROM $2) -
extract('epoch' FROM $1)
WHEN $1  $2 AND $3  INTERVAL '0 seconds' THEN
extract('epoch' FROM $1) -
extract('epoch' FROM $2)
END/extract('epoch' FROM $3)
)::int8
) AS s(i);
$$;

It should be straight-forward to make similar ones to those below.

 CREATE OR REPLACE FUNCTION generate_series
 ( start_ts date
 , end_ts date
 , step interval
 ) RETURNS SETOF date
 
 -- time version
 CREATE OR REPLACE FUNCTION generate_series
 ( start_ts time
 , end_ts time
 , step interval
 ) RETURNS SETOF time

Cheers,
D
-- 
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 415 235 3778AIM: dfetter666
  Skype: davidfetter

Remember to vote!
Consider donating to PostgreSQL: http://www.postgresql.org/about/donate

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [HACKERS] temporal variants of generate_series()

2007-04-25 Thread Neil Conway 
On Thu, 2007-04-12 at 14:56 -0700, Andrew Hammond wrote:
 I've written the following function definitions to extend
 generate_series to support some temporal types (timestamptz, date and
 time). Please include them if there's sufficient perceived need or
 value.

I could see these being useful, but a PL/PgSQL implementation is not
eligible for inclusion in the core backend (since PL/PgSQL is not
enabled by default).

-Neil



---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[HACKERS] temporal variants of generate_series()

2007-04-12 Thread Andrew Hammond 
I've written the following function definitions to extend
generate_series to support some temporal types (timestamptz, date and
time). Please include them if there's sufficient perceived need or
value.

-- timestamptz version
CREATE OR REPLACE FUNCTION generate_series
( start_ts timestamptz
, end_ts timestamptz
, step interval
) RETURNS SETOF timestamptz
AS $$
DECLARE
current_ts timestamptz := start_ts;
BEGIN
IF start_ts  end_ts AND step  INTERVAL '0 seconds' THEN
LOOP
IF current_ts  end_ts THEN
RETURN;
END IF;
RETURN NEXT current_ts;
current_ts := current_ts + step;
END LOOP;
ELSIF end_ts  start_ts AND step  INTERVAL '0 seconds' THEN
LOOP
IF current_ts  end_ts THEN
RETURN;
END IF;
RETURN NEXT current_ts;
current_ts := current_ts + step;
END LOOP;
END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;


-- date version
CREATE OR REPLACE FUNCTION generate_series
( start_ts date
, end_ts date
, step interval
) RETURNS SETOF date
AS $$
DECLARE
current_ts date := start_ts;
BEGIN
IF start_ts  end_ts AND step  INTERVAL '0 seconds' THEN
LOOP
IF current_ts  end_ts THEN
RETURN;
END IF;
RETURN NEXT current_ts;
current_ts := current_ts + step;
END LOOP;
ELSIF end_ts  start_ts AND step  INTERVAL '0 seconds' THEN
LOOP
IF current_ts  end_ts THEN
RETURN;
END IF;
RETURN NEXT current_ts;
current_ts := current_ts + step;
END LOOP;
END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- time version
CREATE OR REPLACE FUNCTION generate_series
( start_ts time
, end_ts time
, step interval
) RETURNS SETOF time
AS $$
DECLARE
current_ts time := start_ts;
BEGIN
IF step  INTERVAL '0 seconds' THEN
LOOP-- handle wraparound first
IF current_ts  end_ts THEN
EXIT;
END IF;
RETURN NEXT current_ts;
current_ts := current_ts + step;
END LOOP;
LOOP
IF current_ts  end_ts THEN
RETURN;
END IF;
RETURN NEXT current_ts;
current_ts := current_ts + step;
END LOOP;
ELSIF step  INTERVAL '0 seconds' THEN
LOOP-- handle wraparound first
IF current_ts  end_ts THEN
EXIT;
END IF;
RETURN NEXT current_ts;
current_ts := current_ts + step;
END LOOP;
LOOP
IF current_ts  end_ts THEN
RETURN;
END IF;
RETURN NEXT current_ts;
current_ts := current_ts + step;
END LOOP;
END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;


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

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