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_idPRIMARY 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_datedate) 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
