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

Reply via email to