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