On 09/08/2010 08:24 AM, Tim Schumacher wrote: >>> 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:
<snip> > As you can see in my example, I'm already using it and this is my > dilemma. Since I can not bring the values of the FROM-Table to the > parameters of my function. Depending on how large your base table is, this might work for you: CREATE TABLE example ( id serial NOT NULL, startdate timestamp without time zone, duration int NOT NULL, CONSTRAINT pq_example_id PRIMARY KEY (id) ); 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 unroll_durations() RETURNS TABLE( example_id integer, duration_date date) AS $$ DECLARE rec1 record; rec2 record; BEGIN FOR rec1 IN SELECT id, startdate, duration FROM example LOOP FOR rec2 IN SELECT to_date(to_char(rec1.startdate,'YYYY-MM-DD'),'YYYY-MM-DD') + s.a as stockdate FROM generate_series(0, rec1.duration - 1) AS s(a) LOOP example_id := rec1.id; duration_date := rec2.stockdate; RETURN NEXT; END LOOP; END LOOP; END; $$ LANGUAGE plpgsql; select * from unroll_durations(); example_id | duration_date ------------+--------------- 1 | 2010-09-03 1 | 2010-09-04 1 | 2010-09-05 1 | 2010-09-06 2 | 2010-09-03 2 | 2010-09-04 2 | 2010-09-05 2 | 2010-09-06 2 | 2010-09-07 2 | 2010-09-08 (10 rows) HTH, Joe -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, & 24x7 Support
signature.asc
Description: OpenPGP digital signature