Re: Division by zero - Mailing list pgsql-general

On 31 Jul 2009, at 19:49, Jeff Davis wrote:

> Yes -- you can do what you want anyway. If you want it to be more
> readable, you can redefine the division operator yourself -- it's
> just a
> function and operator that happens to be named "/" (although that
> might
> not be wise). Maybe you can call it "//" to avoid confusion with
> people
> used to the SQL standard behavior.

Great Idea, that's a very powerful feature, being able to redefine an
operator. I did that as you suggest and it seems to work fine. My
users access postgres through a web app layer so I modified the
application to replace any cases of / with // in calculations as
they're created.

In case there are any improvements to suggest and for the benefit of
anyone else who wants to swallow division by zero, the function and
operator are below. I only use integer and double precision numbers. I
assume that using the more general 'numeric' rather than all
combinations of these would have a performance penalty?

Regards
Oliver Kohll

oliver@gtwm.co.uk / 0845 456 1810 / 07814 828608
www.gtwm.co.uk - company
www.gtportalbase.com - product

---

-- This routine creates an alterantive division operator
-- that doesn't throw an error on a divide by zero
-- but rather returns null

CREATE OR REPLACE FUNCTION gtpb_divide(integer, integer) RETURNS integer
AS 'SELECT $1 / NULLIF($2,0);'
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;

CREATE OR REPLACE FUNCTION gtpb_divide(double precision, double
precision) RETURNS double precision
AS 'SELECT $1 / NULLIF($2,0);'
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;

CREATE OR REPLACE FUNCTION gtpb_divide(double precision, integer)
RETURNS double precision
AS 'SELECT $1 / NULLIF($2,0);'
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;

CREATE OR REPLACE FUNCTION gtpb_divide(integer, double precision)
RETURNS double precision
AS 'SELECT $1 / NULLIF($2,0);'
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;

CREATE OPERATOR // (
PROCEDURE = gtpb_divide,
LEFTARG = integer,
RIGHTARG = integer
);

CREATE OPERATOR // (
PROCEDURE = gtpb_divide,
LEFTARG = double precision,
RIGHTARG = double precision
);

CREATE OPERATOR // (
PROCEDURE = gtpb_divide,
LEFTARG = double precision,
RIGHTARG = integer
);

CREATE OPERATOR // (
PROCEDURE = gtpb_divide,
LEFTARG = integer,
RIGHTARG = double precision
);