From: Joe Conway <mail@joeconway.com>
To: Tim Schumacher <tim.daniel.schumacher@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Generating Rows from a date and a duration
Date: Wed, 08 Sep 2010 09:02:10 -0700
Message-ID: <4C87B382.5080704@joeconway.com> (raw)
In-Reply-To: <8739tkdzrc.wl%tim@we-are-teh-b.org>
References: <4C80C282.700@gmail.com>
<AANLkTikzbog1W-fVcxP341wfsSQ8U7PJoa0c_QB3a7CX@mail.gmail.com>
<8739tkdzrc.wl%tim@we-are-teh-b.org>
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
Attachments:
[application/pgp-signature] signature.asc (898B, ../4C87B382.5080704@joeconway.com/2-signature.asc)
download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mail@joeconway.com, tim.daniel.schumacher@gmail.com
Subject: Re: Generating Rows from a date and a duration
In-Reply-To: <4C87B382.5080704@joeconway.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox