From: Tim Schumacher <tim.daniel.schumacher@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Generating Rows from a date and a duration
Date: Fri, 03 Sep 2010 11:40:18 +0200
Message-ID: <4C80C282.700@gmail.com> (raw)
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
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: tim.daniel.schumacher@gmail.com
Subject: Re: Generating Rows from a date and a duration
In-Reply-To: <4C80C282.700@gmail.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