[SQL] Generating Rows from a date and a duration

[Tim Schumacher]

Hi List,

I'm kinda stuck situation, I have a timestamp which resambles a
startdate and a duration in days and I want to bloat this, so I have a
row for every day beginning from the startdate. I have created an
example bellow, maybe I'm doing it on the wrong angle and you can come
up with some better ideas:

BEGIN TRANSACTION;

CREATE TABLE example
(
   id serial NOT NULL,
   startdate timestamp without time zone,
   duration int_unsigned NOT NULL,
   CONSTRAINT pq_example_id PRIMARY KEY (id)
) WITH (OIDS=FALSE)
;

insert into example(id,startdate,duration) values (1,'2010-09-03',4);
insert into example(id,startdate,duration) values (2,'2010-09-03',6);

CREATE OR REPLACE FUNCTION bloat_duration(IN id integer,
                                          IN startdate  timestamp
without time zone,
                                          IN duration integer,
                                          OUT id integer,
                                          OUT duration_date date)
    RETURNS SETOF RECORD AS
$$
BEGIN
  RETURN QUERY SELECT
id,to_date(to_char(startdate,'YYYY-MM-DD'),'YYYY-MM-DD')+s.a AS
stockdate FROM generate_series(0,duration-1) AS s(a);
END;
$$
LANGUAGE 'plpgsql';

-- This works, but not what I want
SELECT * FROM bloat_duration(1,'2010-09-03',4);

-- This does not work

SELECT * FROM example AS ex
INNER JOIN bloat_duration(ex.id,ex.startdate,ex.duration) AS bd ON bd.id
= ex.id

ROLLBACK TRANSACTION;


greetings

Tim

signature.asc
Description: OpenPGP digital signature