From: Jasen Betts <jasen@xnet.co.nz>
To: pgsql-sql@postgresql.org
Subject: Re: Confused about writing this stored procedure/method.
Date: 23 Aug 2011 10:14:38 GMT
Message-ID: <j2vuie$tte$2@reversiblemaps.ath.cx> (raw)
References: <1314023902196-4723656.post@n5.nabble.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jasen@xnet.co.nz
Subject: Re: Confused about writing this stored procedure/method.
In-Reply-To: <j2vuie$tte$2@reversiblemaps.ath.cx>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox