Received: from magus.postgresql.org ([87.238.57.229]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1T48Vs-0002Sd-T6 for pgsql-sql@postgresql.org; Wed, 22 Aug 2012 10:50:16 +0000 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 1T48Vn-0006YL-Dh for pgsql-sql@postgresql.org; Wed, 22 Aug 2012 10:50:16 +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 1T48VV-00065U-DR; Wed, 22 Aug 2012 11:50:01 +0100 Received: from eddie.ringways.co.uk ([10.1.1.115] helo=eddie.ringways.co.uk) by mail.ringways.co.uk with ESMTP id qBnpX80j12470; Wed, 22 Aug 2012 11:49:51 +0100 From: Gary Stainburn Organization: Ringways Garages Ltd To: Johnny Winn Subject: Re: generated dates from record dates - suggestions Date: Wed, 22 Aug 2012 11:49:53 +0100 User-Agent: KMail/1.9.10 Cc: pgsql-sql@postgresql.org References: <201208201317.46588.gary.stainburn@ringways.co.uk> <201208211131.49999.gary.stainburn@ringways.co.uk> In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <201208221149.53634.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: 20120822 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: On Tuesday 21 August 2012 13:11:06 Johnny Winn wrote: > CREATE OR REPLACE FUNCTION get_dates(date, date, date) RETURNS TABLE(date1 > date, date2 date) > AS $$ > DECLARE > date_1 DATE := NULL; > date_2 DATE := NULL; > BEGIN > > -- test your conditions here > > RETURN QUERY SELECT date_1::date, date_2::date; > END; > $$ > LANGUAGE PLPGSQL; > > I hope this helps, > Johnny [...] 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: -2.1 (--) X-Archive-Number: 201208/25 X-Sequence-Number: 36796 On Tuesday 21 August 2012 13:11:06 Johnny Winn wrote: > CREATE OR REPLACE FUNCTION get_dates(date, date, date) RETURNS TABLE(date1 > date, date2 date) > AS $$ > DECLARE > date_1 DATE := NULL; > date_2 DATE := NULL; > BEGIN > > -- test your conditions here > > RETURN QUERY SELECT date_1::date, date_2::date; > END; > $$ > LANGUAGE PLPGSQL; > > I hope this helps, > Johnny Johnny, Having gone down the CASE/WHEN route and found it too clumsy I'm now looking at using this method. I'm just about to start writing the function, but I'm wondering how I would include this is the select / view . Gary -- Gary Stainburn Group I.T. Manager Ringways Garages http://www.ringways.co.uk