DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=date:message-id:from:to:subject:in-reply-to:references:user-agent
	:organization:mime-version:content-type;
	b=ZyJivNllcirG5MiibOeNSgzLar80+HxTiup33UsKh7uYp/l+jXvnjqzOEc/iG0JSLH
	PH0Hmrrx4iMr6/nKpPTI6HN0yfNDxAq1qni+lflkNwQSHA/uw7QVZcFlFCrFa0I7tO7Z
	G2SanwqPcd1v5L49PVPi8jhVFi194PR8Z4CYI=
Date: Wed, 08 Sep 2010 17:24:39 +0200
Message-ID: <8739tkdzrc.wl%tim@we-are-teh-b.org>
From: Tim Schumacher <tim.daniel.schumacher@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Generating Rows from a date and a duration
In-Reply-To: <AANLkTikzbog1W-fVcxP341wfsSQ8U7PJoa0c_QB3a7CX@mail.gmail.com>
References: <4C80C282.700@gmail.com>
	<AANLkTikzbog1W-fVcxP341wfsSQ8U7PJoa0c_QB3a7CX@mail.gmail.com>
User-Agent: Wanderlust/2.15.9 (Almost Unreal) EMIKO/1.14.1
	(Choanoflagellata) FLIM/1.14.9 (=?UTF-8?B?R29qxY0=?=) APEL/10.7
	EasyPG/1.0.0 Emacs/23.1.50 (x86_64-pc-linux-gnu) MULE/6.0
	(HANACHIRUSATO)
Organization: Secret Conspiracy
MIME-Version: 1.0 (generated by EMIKO 1.14.1 - "Choanoflagellata")
Content-Type: text/plain; charset=US-ASCII

Hi Brian, Hi List,


At Sat, 4 Sep 2010 09:20:53 -0400,
Brian Sherwood wrote:

> On Fri, Sep 3, 2010 at 5:40 AM, Tim Schumacher <
> tim.daniel.schumacher@gmail.com> 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:
> >
> > 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;

> Take a look at the generate_series function in the "set returning functions"
> section of the manual.
> http://www.postgresql.org/docs/8.4/interactive/functions-srf.html

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.

Greetings

Tim

-- 
Compassion -- that's the one things no machine ever had.  Maybe it's
the one thing that keeps men ahead of them.
                -- McCoy, "The Ultimate Computer", stardate 4731.3