From: | Tim Schumacher <tim(dot)daniel(dot)schumacher(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Generating Rows from a date and a duration |
Date: | 2010-09-03 09:40:18 |
Message-ID: | 4C80C282.700@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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_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;
greetings
Tim
From | Date | Subject | |
---|---|---|---|
Next Message | Daniel Sobey | 2010-09-03 10:49:46 | procedure help between databases |
Previous Message | negora | 2010-09-02 12:36:00 | Re: SUM the result of a subquery. |