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.schumac...@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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
