DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:subject
	:x-enigmail-version:content-type;
	b=AmyieQfZCirRNbQAmiak+dkVEOmg87H9E3d0rTn2u+4kssg64UYQWZ9lWyQUfXYRY/
	PhaHhyuvy0OtyFs69PA9gtwjoQb8r7tvZOJclq1Mw+TqGFtkShLW7zvku/96CYZDbTzI
	6NaeHazkokKL/Ep7w4mlikO9J5TzGfVXSrH5U=
Message-ID: <4C80C282.700@gmail.com>
Date: Fri, 03 Sep 2010 11:40:18 +0200
From: Tim Schumacher <tim.daniel.schumacher@gmail.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; de;
	rv:1.9.2.8) Gecko/20100802 Lightning/1.0b2 Thunderbird/3.1.2
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Generating Rows from a date and a duration
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="------------enigD64EE0AB719BD862B1EEEE38"

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

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=3DFALSE)
;

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
=3D ex.id

ROLLBACK TRANSACTION;


greetings

Tim


--------------enigD64EE0AB719BD862B1EEEE38
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 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iQIcBAEBAgAGBQJMgMKCAAoJEAd0ByWoPc27S9QP/2X5Klh2tYGwkMlW6dphcMeq
64TRscqZ4OgvSGuU/ZnYpZnXwe66rr0EZeP9VobDY7ZMXmsj+qAdvxCfNxVIaLvx
5NQU45GjomBfi7ppHUUTWnIr0A/V/nwtrd+s6pjSgAxq7fEV/T9mWnAM4UPYRaSD
v6lwi0jB5czpgP8Mvf640gzohmFKI7AcU3qONfXo9CsqVXPa7ZzkyqUtLWlqOeuG
qQTPg1YYu00TJ1bkeKH7Cviq7kyTG/1P+MZxJIlVHBqPfNkEROaaMjf8PoACyyRv
YX2PCipELR8mGJBto6Xc7u7oKuhLjAoH5L1vkFIPDxFfSOSXNEdGk3BfxqJeX6DI
mHsT7wTgLfy10x6o0zc+WE0ZTfjSFRQHAeB1LwS5S0K5agDyxDlEUp9OReCT8QaC
uCiVL8ro8HCgHqTEtBpu4LAnOcxPbVPfNbodroCGFnd0n52LGCorxdi4oi0QIP9r
KmrImi64FuB7vpESiWjasuqael/Po5bHkNjwafoGVretP07xcjdQ7A+VvX/f04Gm
Nh1jXfDPjeStjpnoUu5VQYxlt63qaJau7pc/DGOH39gXjzLTEeSG5McGniq1fsVg
QY/Hs1qDK3G1iI0lyX7OiNEIkWVpol42YRu0dh6FHgUYd/DPmPhuKEV7SjRd4UgA
sVfpa0XeI7trem6XDesk
=UaKi
-----END PGP SIGNATURE-----

--------------enigD64EE0AB719BD862B1EEEE38--