Thread: Confused about writing this stored procedure/method.
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.
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 >
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
(anonymous) wrote: > [...] > 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 > [...] You don't need any function for that, just use "ORDER BY Month < 4, Month". Tim