Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 262EE43CEB9 for ; Thu, 19 Jan 2012 13:17:33 -0400 (AST) Received: from mail-iy0-f174.google.com ([209.85.210.174]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1RnvcB-0003tx-8b for pgsql-sql@postgresql.org; Thu, 19 Jan 2012 17:17:33 +0000 Received: by iagf6 with SMTP id f6so161506iag.19 for ; Thu, 19 Jan 2012 09:17:17 -0800 (PST) MIME-Version: 1.0 Received: by 10.42.145.131 with SMTP id f3mr27447652icv.8.1326993437867; Thu, 19 Jan 2012 09:17:17 -0800 (PST) Received: by 10.50.161.227 with HTTP; Thu, 19 Jan 2012 09:17:17 -0800 (PST) X-Originating-IP: [66.215.95.51] In-Reply-To: <201201190820.06564.adrian.klaver@gmail.com> References: <201201190713.34333.gary.stainburn@ringways.co.uk> <201201191516.16802.gary.stainburn@ringways.co.uk> <201201191559.27698.gary.stainburn@ringways.co.uk> <201201190820.06564.adrian.klaver@gmail.com> Date: Thu, 19 Jan 2012 09:17:17 -0800 Message-ID: Subject: Re: date range to set of dates expansion From: Samuel Gendler To: Adrian Klaver Cc: pgsql-sql@postgresql.org, Gary Stainburn Content-Type: multipart/alternative; boundary=90e6ba6e86fc7bc00004b6e4bca8 X-Pg-Spam-Score: -2.6 (--) X-Archive-Number: 201201/94 X-Sequence-Number: 36265 --90e6ba6e86fc7bc00004b6e4bca8 Content-Type: text/plain; charset=ISO-8859-1 On Thu, Jan 19, 2012 at 8:20 AM, Adrian Klaver 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 --90e6ba6e86fc7bc00004b6e4bca8 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable

On Thu, Jan 19, 2012 at 8:20 AM, Adrian = Klaver <adr= ian.klaver@gmail.com> wrote:
On Thursday, January 19, 2012 7:59:27 am Gary Stainburn w= rote:
> 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,
> =A0 =A0 =A0 =A0date_range (asdate, afdate)::date AS asdate,
> =A0 =A0 =A0 =A0acomments
> FROM availability
>
> In 8.4 it returns the expanded dataset as required. In 8.3 I get:
>
> ERROR: =A0set-valued function called in context that cannot accept a s= et
> CONTEXT: =A0PL/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 funct= ions 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. =A0You 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, afda= te) 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. =A0I'm in a rush, s= o no time to figure out a working demo for you.

--sam

--90e6ba6e86fc7bc00004b6e4bca8--