Re: Calculating percentages in Postgresql - Mailing list pgsql-general

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