Обсуждение: 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