Thread: Generating Rows from a date and a duration
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
Hi Brian, Hi List, At Sat, 4 Sep 2010 09:20:53 -0400, Brian Sherwood wrote: > On Fri, Sep 3, 2010 at 5:40 AM, Tim Schumacher < > tim.daniel.schumacher@gmail.com> 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: > > > > 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; > Take a look at the generate_series function in the "set returning functions" > section of the manual. > http://www.postgresql.org/docs/8.4/interactive/functions-srf.html 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. Greetings Tim -- Compassion -- that's the one things no machine ever had. Maybe it's the one thing that keeps men ahead of them. -- McCoy, "The Ultimate Computer", stardate 4731.3
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