From: | Jim Nasby <decibel(at)decibel(dot)org> |
---|---|
To: | David Fetter <david(at)fetter(dot)org> |
Cc: | Andrew Hammond <andrew(dot)george(dot)hammond(at)gmail(dot)com>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: temporal variants of generate_series() |
Date: | 2007-05-01 09:36:21 |
Message-ID: | 745BFDEB-76EA-4F02-B223-9E5C2A8DF549@decibel.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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 jim(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
From | Date | Subject | |
---|---|---|---|
Next Message | rupesh bajaj | 2007-05-01 09:39:17 | How to hidde the column from the user |
Previous Message | Martijn van Oosterhout | 2007-05-01 09:36:10 | Re: [HACKERS] SOS, help me please, one problem towards the postgresql developement on windows |