Generating Rows from a date and a duration - Mailing list pgsql-sql

From Tim Schumacher
Subject Generating Rows from a date and a duration
Date
Msg-id 4C80C282.700@gmail.com
Whole thread Raw
List pgsql-sql
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


pgsql-sql by date:

Previous
From: negora
Date:
Subject: Re: SUM the result of a subquery.
Next
From: Daniel Sobey
Date:
Subject: procedure help between databases