Hello
2011/8/22 JavaNoobie <vivek.mv@enzentech.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>