To: pgsql-sql@postgresql.org
From: Jasen Betts <jasen@xnet.co.nz>
Subject: Re: Confused about writing this stored procedure/method.
Date: 23 Aug 2011 10:14:38 GMT
Organization: Dis (not Dat) Organisation
Lines: 27
Message-ID: <j2vuie$tte$2@reversiblemaps.ath.cx>
References: <1314023902196-4723656.post@n5.nabble.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
User-Agent: slrn/pre1.0.0-18 (Linux)

On 2011-08-22, JavaNoobie <vivek.mv@enzentech.com> wrote:
> Hi All,
> I'm trying to write a stored procedure /function to re-order a set of
> calendar months.I have a set of calendar months stored from January to
> December in my tables. And as of now when I do order by on this column  ,
> the data is ordered alphabetically , starting April, august  etc. and so on
> I want to order these months starting from April through March in order to
> sync with the financial calendar . I'm trying to write a stored procedure to
> do the same (I'm not aware of any other method that Postgres offers  this
> reordering , if there's any , please do let me know!).

 order by (case month when 'January' then 1 when 'February' then 2 
 ...[I'm too lazy to type the rest]...  when 'December' then 12 end)
   
get the idea? (except change the numbers to match financial calendar)

you can index on that expression too

if you have them as numbers instead of words you can use an array
instead of the case.

For as task like this an SQL function may be more efficient than a
PLPGSQL function.


-- 
⚂⚃ 100% natural