pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedConfused about writing this stored procedure/method.
4+ messages / 4 participants
[nested] [flat]
* Confused about writing this stored procedure/method.
@ 2011-08-22 14:38 JavaNoobie <vivek.mv@enzentech.com>
0 siblings, 3 replies; 4+ messages in thread
From: JavaNoobie @ 2011-08-22 14:38 UTC (permalink / raw)
To: pgsql-sql
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-tp472365...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Confused about writing this stored procedure/method.
@ 2011-08-22 16:05 Pavel Stehule <pavel.stehule@gmail.com>
parent: JavaNoobie <vivek.mv@enzentech.com>
2 siblings, 0 replies; 4+ messages in thread
From: Pavel Stehule @ 2011-08-22 16:05 UTC (permalink / raw)
To: JavaNoobie <vivek.mv@enzentech.com>; +Cc: pgsql-sql
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-tp472365...
> 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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Confused about writing this stored procedure/method.
@ 2011-08-23 10:14 Jasen Betts <jasen@xnet.co.nz>
parent: JavaNoobie <vivek.mv@enzentech.com>
2 siblings, 0 replies; 4+ messages in thread
From: Jasen Betts @ 2011-08-23 10:14 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Confused about writing this stored procedure/method.
@ 2011-08-24 14:07 Tim Landscheidt <tim@tim-landscheidt.de>
parent: JavaNoobie <vivek.mv@enzentech.com>
2 siblings, 0 replies; 4+ messages in thread
From: Tim Landscheidt @ 2011-08-24 14:07 UTC (permalink / raw)
To: pgsql-sql
(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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2011-08-24 14:07 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-08-22 14:38 Confused about writing this stored procedure/method. JavaNoobie <vivek.mv@enzentech.com>
2011-08-22 16:05 ` Pavel Stehule <pavel.stehule@gmail.com>
2011-08-23 10:14 ` Jasen Betts <jasen@xnet.co.nz>
2011-08-24 14:07 ` Tim Landscheidt <tim@tim-landscheidt.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox