Re: Calculating percentages in Postgresql

am Tue, dem 21.11.2006, um 14:59:16 +0200 mailte Peter Nixonn folgendes:
> Hi Guys
>
>
> Has anyone written a postgres function to calculate percentages without
> giving "ERROR: division by zero" when fed zeros?

Quick & simple:

create or replace function percentage(float,float) returns float as $$
begin
if $2 = 0 then
return NULL;
else
return 100*$1/$2;
end if;
end;
$$ language plpgsql immutable;

Example:

test=*> select percentage(5,10);
percentage
------------
50
(1 row)

test=*> select percentage(5,0);
percentage
------------

(1 row)

test=*> select coalesce(percentage(5,0),0);
coalesce
----------
0
(1 row)

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net