pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feeddate range to set of dates expansion
10+ messages / 5 participants
[nested] [flat]
* date range to set of dates expansion
@ 2012-01-19 07:13 Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 1 reply; 10+ messages in thread
From: Gary Stainburn @ 2012-01-19 07:13 UTC (permalink / raw)
To: pgsql-sql
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.
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: date range to set of dates expansion
@ 2012-01-19 08:32 hari.fuchs@gmail.com
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 1 reply; 10+ messages in thread
From: hari.fuchs@gmail.com @ 2012-01-19 08:32 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: date range to set of dates expansion
@ 2012-01-19 15:11 Gary Stainburn <gary.stainburn@ringways.co.uk>
parent: hari.fuchs@gmail.com
0 siblings, 1 reply; 10+ messages in thread
From: Gary Stainburn @ 2012-01-19 15:11 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: date range to set of dates expansion
@ 2012-01-19 15:16 Gary Stainburn <gary.stainburn@ringways.co.uk>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 2 replies; 10+ messages in thread
From: Gary Stainburn @ 2012-01-19 15:16 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: date range to set of dates expansion
@ 2012-01-19 15:59 Gary Stainburn <gary.stainburn@ringways.co.uk>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
1 sibling, 1 reply; 10+ messages in thread
From: Gary Stainburn @ 2012-01-19 15:59 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: date range to set of dates expansion
@ 2012-01-19 16:20 Adrian Klaver <adrian.klaver@gmail.com>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 1 reply; 10+ messages in thread
From: Adrian Klaver @ 2012-01-19 16:20 UTC (permalink / raw)
To: pgsql-sql; +Cc: Gary Stainburn <gary.stainburn@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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: date range to set of dates expansion
@ 2012-01-19 16:50 Steve Crawford <scrawford@pinpointresearch.com>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
1 sibling, 1 reply; 10+ messages in thread
From: Steve Crawford @ 2012-01-19 16:50 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: date range to set of dates expansion
@ 2012-01-19 17:17 Samuel Gendler <sgendler@ideasculptor.com>
parent: Adrian Klaver <adrian.klaver@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Samuel Gendler @ 2012-01-19 17:17 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@gmail.com>; +Cc: pgsql-sql@postgresql.org, Gary Stainburn <gary.stainburn@ringways.co.uk>
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:
> > 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.
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: date range to set of dates expansion
@ 2012-01-19 17:27 Gary Stainburn <gary.stainburn@ringways.co.uk>
parent: Steve Crawford <scrawford@pinpointresearch.com>
0 siblings, 0 replies; 10+ messages in thread
From: Gary Stainburn @ 2012-01-19 17:27 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: date range to set of dates expansion
@ 2012-01-19 19:42 Adrian Klaver <adrian.klaver@gmail.com>
parent: Samuel Gendler <sgendler@ideasculptor.com>
0 siblings, 0 replies; 10+ messages in thread
From: Adrian Klaver @ 2012-01-19 19:42 UTC (permalink / raw)
To: Samuel Gendler <sgendler@ideasculptor.com>; +Cc: pgsql-sql@postgresql.org, Gary Stainburn <gary.stainburn@ringways.co.uk>
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
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2012-01-19 19:42 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-01-19 07:13 date range to set of dates expansion Gary Stainburn <gary.stainburn@ringways.co.uk>
2012-01-19 08:32 ` hari.fuchs@gmail.com
2012-01-19 15:11 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2012-01-19 15:16 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2012-01-19 15:59 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2012-01-19 16:20 ` Adrian Klaver <adrian.klaver@gmail.com>
2012-01-19 17:17 ` Samuel Gendler <sgendler@ideasculptor.com>
2012-01-19 19:42 ` Adrian Klaver <adrian.klaver@gmail.com>
2012-01-19 16:50 ` Steve Crawford <scrawford@pinpointresearch.com>
2012-01-19 17:27 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
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