Thread: date range to set of dates expansion
Hi,
How can I expand a date range in a table to a set of date records?
I have a table of availabilities thus:
Column | Type | Modifiers
-----------+-----------------------------+------------------------------------------------------------aid |
integer | not null default
nextval('availability_aid_seq'::regclass)asid | integer | not nullasdate | date
| not nullafdate | date | not nulladays | integer
|acomments| text |
asdate is the start date
afdate is the finish date
How can I expand this to a set of
Column | Type | Modifiers
-----------+-----------------------------+------------------------------------------------------------aid |
integer | not null asid | integer | not nulladate | date
| not nullacomments | text |
i.e.
aid | asid | asdate | afdate | adays | acomments
-----+------+------------+------------+-------+-------------------- 12 | 1 | 2007-08-11 | 2007-08-12 | 1 | Early
finishSunday
Becomes
aid | asid | asdate | acomments
-----+------+------------+-------------------- 12 | 1 | 2007-08-11 | Early finish Sunday 12 | 1 | 2007-08-12 |
Earlyfinish Sunday
I have a function date_range to return a set of dates, but so far I can't get
a valid view to work.
Also, is there a better method?
CREATE FUNCTION date_range(fdate date, tdate date) RETURNS SETOF date AS $$
DECLARE wdate date;
BEGIN return next fdate; wdate:=fdate+1; while wdate <= tdate LOOP return next wdate; wdate:=wdate+1; end LOOP;
return;
END;
$$ LANGUAGE plpgsql;
--
Gary Stainburn
Group I.T. Manager
Ringways Garages
http://www.ringways.co.uk
Gary Stainburn <gary.stainburn@ringways.co.uk> writes:
> Hi,
>
> How can I expand a date range in a table to a set of date records?
>
> I have a table of availabilities thus:
>
> Column | Type | Modifiers
> -----------+-----------------------------+------------------------------------------------------------
> aid | integer | not null default
> nextval('availability_aid_seq'::regclass)
> asid | integer | not null
> asdate | date | not null
> afdate | date | not null
> adays | integer |
> acomments | text |
>
>
> asdate is the start date
> afdate is the finish date
>
> How can I expand this to a set of
>
> Column | Type | Modifiers
> -----------+-----------------------------+------------------------------------------------------------
> aid | integer | not null
> asid | integer | not null
> adate | date | not null
> acomments | text |
>
> i.e.
>
> aid | asid | asdate | afdate | adays | acomments
> -----+------+------------+------------+-------+--------------------
> 12 | 1 | 2007-08-11 | 2007-08-12 | 1 | Early finish Sunday
>
> Becomes
>
> aid | asid | asdate | acomments
> -----+------+------------+--------------------
> 12 | 1 | 2007-08-11 | Early finish Sunday
> 12 | 1 | 2007-08-12 | Early finish Sunday
>
> I have a function date_range to return a set of dates, but so far I can't get
> a valid view to work.
Why don't you just use the built-in PostgreSQL function for that?
SELECT aid, asid, generate_series (asdate, afdate, INTERVAL '1 day')::date AS asdate, acomments
FROM tbl
On Thursday 19 January 2012 08:32:27 hari.fuchs@gmail.com wrote: > > Why don't you just use the built-in PostgreSQL function for that? > > SELECT aid, asid, > generate_series (asdate, afdate, INTERVAL '1 day')::date AS asdate, > acomments > FROM tbl 1) because I didn't know about it 2) because the version of postgresql I run doesn't support it. However, it does exactly what I need so thanks very much. I'll be upgrading my live server as soon as possible, but in the meantime can anyone suggest a way I can do the same thing using Postgresql 8.1 until I can evaluate 8.4 on my live systems? -- Gary Stainburn Group I.T. Manager Ringways Garages http://www.ringways.co.uk
On Thursday 19 January 2012 15:11:46 Gary Stainburn wrote: > I'll be upgrading my live server as soon as possible, but in the meantime > can anyone suggest a way I can do the same thing using Postgresql 8.1 > until I can evaluate 8.4 on my live systems? Sorry, I meant 8.3 as my current version -- Gary Stainburn Group I.T. Manager Ringways Garages http://www.ringways.co.uk
The following code works in 8.4 but not 8.3. Anyone know why, or what I need to do to change it? SELECT aid, asid, date_range (asdate, afdate)::date AS asdate, acomments FROM availability In 8.4 it returns the expanded dataset as required. In 8.3 I get: ERROR: set-valued function called in context that cannot accept a set CONTEXT: PL/pgSQL function "date_range" line 4 at RETURN NEXT Is there a way to use the integer only generate_series in 8.3 to generate dates by typecasting to/from integers? -- Gary Stainburn Group I.T. Manager Ringways Garages http://www.ringways.co.uk
Sorry for using the list as a scratch-pad for my brain. select aid, asid, generate_series(asdate-'1970-01-01'::date, afdate-'1970-01-01'::date)+'1970-01-01'::date as adate, acomments from availability; Has done the trick. -- Gary Stainburn Group I.T. Manager Ringways Garages http://www.ringways.co.uk
On Thursday, January 19, 2012 7:59:27 am Gary Stainburn wrote: > The following code works in 8.4 but not 8.3. > Anyone know why, or what I need to do to change it? > > SELECT aid, asid, > date_range (asdate, afdate)::date AS asdate, > acomments > FROM availability > > In 8.4 it returns the expanded dataset as required. In 8.3 I get: > > ERROR: set-valued function called in context that cannot accept a set > CONTEXT: PL/pgSQL function "date_range" line 4 at RETURN NEXT As to why it works in 8.4 vs 8.3 http://www.postgresql.org/docs/8.4/interactive/release-8-4.html "Support set-returning functions in SELECT result lists even for functions that return their result via a tuplestore (Tom) In particular, this means that functions written in PL/pgSQL and other PL languages can now be called this way.' In 8.3- I believe you could only call it as SELECT * from date_range (asdate, afdate)::date AS asdate; > > Is there a way to use the integer only generate_series in 8.3 to generate > dates by typecasting to/from integers? -- Adrian Klaver adrian.klaver@gmail.com
On 01/19/2012 07:16 AM, Gary Stainburn wrote: > On Thursday 19 January 2012 15:11:46 Gary Stainburn wrote: >> I'll be upgrading my live server as soon as possible, but in the meantime >> can anyone suggest a way I can do the same thing using Postgresql 8.1 >> until I can evaluate 8.4 on my live systems? I'm sure most here would recommend moving to 9.1 rather than 8.4. Better performance, cooler replication functionality, more advanced in-place upgrade capabilities for future upgrades, a couple years longer before end-of-life, advances to windowing functions and other SQL commands and much other goodness. Cheers, Steve
On Thu, Jan 19, 2012 at 8:20 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
On Thursday, January 19, 2012 7:59:27 am Gary Stainburn wrote:As to why it works in 8.4 vs 8.3
> The following code works in 8.4 but not 8.3.
> Anyone know why, or what I need to do to change it?
>
> SELECT aid, asid,
> date_range (asdate, afdate)::date AS asdate,
> acomments
> FROM availability
>
> In 8.4 it returns the expanded dataset as required. In 8.3 I get:
>
> ERROR: set-valued function called in context that cannot accept a set
> CONTEXT: PL/pgSQL function "date_range" line 4 at RETURN NEXT
http://www.postgresql.org/docs/8.4/interactive/release-8-4.html
"Support set-returning functions in SELECT result lists even for functions that
return their result via a tuplestore (Tom)
In particular, this means that functions written in PL/pgSQL and other PL
languages can now be called this way.'
In 8.3- I believe you could only call it as
SELECT * from date_range (asdate, afdate)::date AS asdate;
I don't think you can have that cast there when it is in the from-clause. You can refer to its values in the select clause explicitly if you alias the the results:
select r.range_date::date from date_range(asfdate, afdate) as r(range_date); Presumably, you can fashion something that satisfies your needs by putting something like this in a subquery which refers to the start and end date in each row of the outer query. I'm in a rush, so no time to figure out a working demo for you.
--sam
On 01/19/2012 09:17 AM, Samuel Gendler wrote: > > > On Thu, Jan 19, 2012 at 8:20 AM, Adrian Klaver <adrian.klaver@gmail.com > <mailto:adrian.klaver@gmail.com>> wrote: > > On Thursday, January 19, 2012 7:59:27 am Gary Stainburn wrote: > > The following code works in 8.4 but not 8.3. > > Anyone know why, or what I need to do to change it? > > > > SELECT aid, asid, > > date_range (asdate, afdate)::date AS asdate, > > acomments > > FROM availability > > > > In 8.4 it returns the expanded dataset as required. In 8.3 I get: > > > > ERROR: set-valued function called in context that cannot accept > a set > > CONTEXT: PL/pgSQL function "date_range" line 4 at RETURN NEXT > > As to why it works in 8.4 vs 8.3 > > http://www.postgresql.org/docs/8.4/interactive/release-8-4.html > > "Support set-returning functions in SELECT result lists even for > functions that > return their result via a tuplestore (Tom) > > In particular, this means that functions written in PL/pgSQL and > other PL > languages can now be called this way.' > > In 8.3- I believe you could only call it as > > SELECT * from date_range (asdate, afdate)::date AS asdate; > > > I don't think you can have that cast there when it is in the > from-clause. That was a cut and paste error on my part, I just copied that line from the original query. > -- Adrian Klaver adrian.klaver@gmail.com
On Thursday 19 January 2012 16:50:53 Steve Crawford wrote: > I'm sure most here would recommend moving to 9.1 rather than 8.4. Better > performance, cooler replication functionality, more advanced in-place > upgrade capabilities for future upgrades, a couple years longer before > end-of-life, advances to windowing functions and other SQL commands and > much other goodness. > > Cheers, > Steve Thanks for this Steve. I would have had a look at whatever the latest version is before proceeding. However, I'm running this on an old Fedora 9 box and like to stick to using RPM's. Can I upgrade to 9.1 on a FC9 system using RPM's? Also, the last time I did a server upgrade (FC4 to the FC9 system) upgrading apache, PHP and postgresql broke so many things in my applications it was painful. Can anyone suggest ways I can soak test my systems before upgrading the live system? -- Gary Stainburn Group I.T. Manager Ringways Garages http://www.ringways.co.uk