Re: Confused about writing this stored procedure/method.

Hello

2011/8/22 JavaNoobie <vivek(dot)mv(at)enzentech(dot)com>:
> 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)

this is wrong - you can use only a variable name and type name in
parameter's list

> 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;

probably you would to use a SELECT INTO ...

> RETURN qty;
> END;
> $$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION getNMonth(num int)
RETURNS int AS $$
DECLARE qty int;
BEGIN
SELECT CASE num
WHEN 4 THEN 1
WHEN 5 THEN 2
.. INTO qty;
RETURN qty;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;

try to read a documentation first, please

http://www.postgresql.org/docs/9.0/interactive/plpgsql.html

Regards

Pavel Stehule

> 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.
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>