pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedGenerating Rows from a date and a duration
3+ messages / 2 participants
[nested] [flat]
* Generating Rows from a date and a duration
@ 2010-09-03 09:40 Tim Schumacher <tim.daniel.schumacher@gmail.com>
2010-09-08 15:24 ` Re: Generating Rows from a date and a duration Tim Schumacher <tim.daniel.schumacher@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tim Schumacher @ 2010-09-03 09:40 UTC (permalink / raw)
To: 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
Attachments:
[application/pgp-signature] signature.asc (897B, ../../4C80C282.700@gmail.com/2-signature.asc)
download
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Generating Rows from a date and a duration
2010-09-03 09:40 Generating Rows from a date and a duration Tim Schumacher <tim.daniel.schumacher@gmail.com>
@ 2010-09-08 15:24 ` Tim Schumacher <tim.daniel.schumacher@gmail.com>
2010-09-08 16:02 ` Re: Generating Rows from a date and a duration Joe Conway <mail@joeconway.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tim Schumacher @ 2010-09-08 15:24 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Generating Rows from a date and a duration
2010-09-03 09:40 Generating Rows from a date and a duration Tim Schumacher <tim.daniel.schumacher@gmail.com>
2010-09-08 15:24 ` Re: Generating Rows from a date and a duration Tim Schumacher <tim.daniel.schumacher@gmail.com>
@ 2010-09-08 16:02 ` Joe Conway <mail@joeconway.com>
0 siblings, 0 replies; 3+ messages in thread
From: Joe Conway @ 2010-09-08 16:02 UTC (permalink / raw)
To: Tim Schumacher <tim.daniel.schumacher@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2010-09-08 16:02 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-09-03 09:40 Generating Rows from a date and a duration Tim Schumacher <tim.daniel.schumacher@gmail.com>
2010-09-08 15:24 ` Tim Schumacher <tim.daniel.schumacher@gmail.com>
2010-09-08 16:02 ` Joe Conway <mail@joeconway.com>
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