Обсуждение: generated dates from record dates - suggestions
Hi folks. I've got a table with three dates which are populated from an external source. I then want to have a view with two calculated dates in it, e.g. if date_1 is null and date_2 is null then date_a=NULL if date_1 is not null and date 2 is null then date_a=date_1+'90 days' if date_1 > date3 rhen date_b=date1 else date_b=date2 etc. What's the best way to do this? I know it's a quite open question but I'm interested to hear different responses -- Gary Stainburn Group I.T. Manager Ringways Garages http://www.ringways.co.uk
With a CASE ... WHEN statement? Best, Oliver ----- Original Message ----- From: "Gary Stainburn" <gary.stainburn@ringways.co.uk> To: <pgsql-sql@postgresql.org> Sent: Monday, August 20, 2012 1:17 PM Subject: [SQL] generated dates from record dates - suggestions > Hi folks. > > I've got a table with three dates which are populated from an external > source. > I then want to have a view with two calculated dates in it, e.g. > > if date_1 is null and date_2 is null then date_a=NULL > if date_1 is not null and date 2 is null then date_a=date_1+'90 days' > if date_1 > date3 rhen date_b=date1 else date_b=date2 > etc. > > What's the best way to do this? > > I know it's a quite open question but I'm interested to hear different > responses > > > -- > Gary Stainburn > Group I.T. Manager > Ringways Garages > http://www.ringways.co.uk > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql
On Tuesday 21 August 2012 02:01:55 Johnny Winn wrote: > I would define a function and use it to abstract the expected behavior. It > would appear that there are several conditions so this abstraction would > provide for both usability and maintainability. > > - Johnny Johnny, This was what I was wondering, and was looking for suggestions / best practices on how to do this. I had first thought of embedding case/when statements in the view but it could easily become unweildy -- Gary Stainburn Group I.T. Manager Ringways Garages http://www.ringways.co.uk
On 2012-08-20, Gary Stainburn <gary.stainburn@ringways.co.uk> wrote: > Hi folks. > > I've got a table with three dates which are populated from an external source. > I then want to have a view with two calculated dates in it, e.g. > > if date_1 is null and date_2 is null then date_a=NULL > if date_1 is not null and date 2 is null then date_a=date_1+'90 days' > if date_1 > date3 rhen date_b=date1 else date_b=date2 > etc. > > What's the best way to do this? CASE looks good. > I know it's a quite open question but I'm interested to hear different > responses "date_b" on line three, is that a typo? if not how am i to interpret it? -- ⚂⚃ 100% natural
On Tuesday 21 August 2012 12:14:53 Jasen Betts wrote: > > What's the best way to do this? > > CASE looks good. > > > I know it's a quite open question but I'm interested to hear different > > responses > > "date_b" on line three, is that a typo? if not how am i to interpret > it? The table contains date_1, date_2 and date_3. The resulting view needs to contain date_1, date_2, date_3, date_a and date_b where date_a and date_b are calculated based on the first three (plus a text field). -- Gary Stainburn Group I.T. Manager Ringways Garages http://www.ringways.co.uk
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