Date: Mon, 22 Aug 2011 07:38:22 -0700 (PDT)
From: JavaNoobie <vivek.mv@enzentech.com>
To: pgsql-sql@postgresql.org
Message-ID: <1314023902196-4723656.post@n5.nabble.com>
Subject: Confused about writing this stored procedure/method.
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

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!).
I intend pass the number of the month(say 1 for January , 2 for February
etc>) as the parameter to this method and return a number that corresponds
to the index of the month in the new order , say I pass 8 for August , I
return 11 , in order to get the  index of August in he financial year
calendar somewhat like this

CREATE FUNCTION getNMonth(to_number(domain.description,'MM') int as num)
RETURNS int AS $$
DECLARE
qty int;
BEGIN
SELECT qty,
       CASE WHEN num=4 THEN 1-- Set August to the first month etc.
            WHEN num=5 THEN 2
            ELSE 'other'
       END
    FROM  DOMAIN;
RETURN qty;
END;
$$ LANGUAGE plpgsql;
However, this throws a syntax error on to_number. This my first attempt at a
stored procedure in Postgres .Thank you for your time.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Confused-about-writing-this-stored-procedure-method-tp4723656p4723656.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.