Message-ID: <4C87B382.5080704@joeconway.com>
Date: Wed, 08 Sep 2010 09:02:10 -0700
From: Joe Conway <mail@joeconway.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.8.0.12) Gecko/20071019 Fedora/1.5.0.12-3.fc6 pango-text
	Thunderbird/1.5.0.12 Mnenhy/0.7.5.666
MIME-Version: 1.0
To: Tim Schumacher <tim.daniel.schumacher@gmail.com>
CC: pgsql-sql@postgresql.org
Subject: Re: Generating Rows from a date and a duration
References: <4C80C282.700@gmail.com>
	<AANLkTikzbog1W-fVcxP341wfsSQ8U7PJoa0c_QB3a7CX@mail.gmail.com>
	<8739tkdzrc.wl%tim@we-are-teh-b.org>
In-Reply-To: <8739tkdzrc.wl%tim@we-are-teh-b.org>
OpenPGP: id=B699434A
Content-Type: multipart/signed; micalg=pgp-sha256;
	protocol="application/pgp-signature";
	boundary="------------enigB6F00A2C3A9397F8005EB30E"

This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enigB6F00A2C3A9397F8005EB30E
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

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 com=
e
>>> 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    :=3D rec1.id;
      duration_date :=3D 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

--=20
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support


--------------enigB6F00A2C3A9397F8005EB30E
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/

iQIcBAEBCAAGBQJMh7OCAAoJEDfy90M199hllHkP/RTp+RzHfnNLjRUPexyf1suv
S/3g7bixthxQOWke4jPwwQk7xzhDbUhJqU7Aq0HN5ynqUJn0xMnKD2x6NXqXEosT
Yqn7GkZ0rKTVQTJ043ykanoHWlBKGWm1JEZRIygIZDBrB6HgiPelqinueKY1VpsF
YJ0F8FOlHKwoyjW97m2pmpzpkKV8n6qJuvcvLDsWaC4xWXFBh9V9ufNbwqs1ru4+
9oIewTNU6HWw6s957AyJGfak5VOHmx3LiqKD43M/R/UDFk+O3uNTkOfpQaUDedUG
tO4ObD9fQRqqsOaBdIKINc+uRgF9rGgqUAeOYjc15Hn09BC70mFQF1x6uP5nztra
5iA2jNg8VJWaJwLgHpraTwKqDsaBv1AiaOstwKW6HVdEM0itzVI9/agU3mKstdU+
hnLPOPgmyBYaRrb4TZ99QKDiV4ufUNVqiMesv0MT7R+pgP1p+9WayouKlZIpXRVE
kKfLu+eXry+8UHRtzYSUgeKEoAE6ox1tdTq4s+12qwj46SzVuBYo61mKE5yDzK1w
i0tlLflD/Ixz7rDmd3o23cmEs2N4bvMYHxWAocNrDiUdqZ/TJReI5hVV3qNWFBo5
EIQRkfJWHNItX+4wUHyLD3yqnZxbaoRvFXelMpGhMScgKme3x5ibfGZyys1BcR9t
ScVaz/VgweCLb//x8xO4
=WM6b
-----END PGP SIGNATURE-----

--------------enigB6F00A2C3A9397F8005EB30E--