Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id A2CC643CEB9 for ; Thu, 19 Jan 2012 11:59:49 -0400 (AST) Received: from hub.ringways.co.uk ([77.86.27.30] helo=mail.ringways.co.uk) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1RnuOx-0002IP-IA for pgsql-sql@postgresql.org; Thu, 19 Jan 2012 15:59:49 +0000 Received: from localhost ([127.0.0.1] helo=mail.ringways.co.uk) by mail.ringways.co.uk with esmtp (Exim 4.69) (envelope-from ) id 1RnuOg-0004Xn-0Y for pgsql-sql@postgresql.org; Thu, 19 Jan 2012 15:59:31 +0000 Received: from eddie.ringways.co.uk ([10.1.1.115] helo=eddie.ringways.co.uk) by mail.ringways.co.uk with ESMTP id qFxOuiIh35228; Thu, 19 Jan 2012 15:59:24 +0000 From: Gary Stainburn Organization: Ringways Garages Ltd To: pgsql-sql@postgresql.org Subject: Re: date range to set of dates expansion Date: Thu, 19 Jan 2012 15:59:27 +0000 User-Agent: KMail/1.9.10 References: <201201190713.34333.gary.stainburn@ringways.co.uk> <201201191511.46076.gary.stainburn@ringways.co.uk> <201201191516.16802.gary.stainburn@ringways.co.uk> In-Reply-To: <201201191516.16802.gary.stainburn@ringways.co.uk> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <201201191559.27698.gary.stainburn@ringways.co.uk> X-SpamTest-Envelope-From: gary.stainburn@ringways.co.uk X-SpamTest-Info: Profiles 20401 [Mar 31 2011] X-SpamTest-Method: none X-SpamTest-Rate: 0 X-SpamTest-Status: Not detected X-SpamTest-Status-Extended: not_detected X-SpamTest-Version: SMTP-Filter Version 3.0.0 [0285], KAS30/SDK/Release X-Anti-Virus: Kaspersky Mail Gateway, version: 5.6.28/RELEASE, bases: 20110331T110535 #5151353, check: 20120119 clean X-Spam-Score: -51.6 (---------------------------------------------------) X-Spam-Report: Spam detection software, running on the system "ollie.ringways.co.uk", has identified this incoming email as possible spam. The original message has been attached to this so you can view it (if it isn't spam) or label similar future email. If you have any questions, see Gary Stainburn for details. Content preview: 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: [...] Content analysis details: (-51.6 points, 15.0 required) pts rule name description ---- ---------------------- -------------------------------------------------- -50 ALL_TRUSTED Passed through trusted hosts only via SMTP -2.6 BAYES_00 BODY: Bayesian spam probability is 0 to 1% [score: 0.0000] 1.0 RING_SAFE RING_SAFE X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201201/90 X-Sequence-Number: 36261 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