Re: Division by zero

On Sun, Aug 02, 2009 at 12:08:28PM +0100, Oliver Kohll - Mailing Lists wrote:
> -- 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;

If I were you I'd remove the "RETURNS NULL ON NULL INPUT" flag. I used
to think of it as just a "hint" to the planner as to its behavior,
but it turns out that it's interpreted much more strongly by PG. The
interpretation means that the function doesn't end up getting be inlined
where I'd expect it to be and hence the optimizer doesn't get as much
freedom to rewrite your queries as you may want.

Admittedly it's going to be less of an issue with division that other
operators, but it's worth bearing in mind. The "IMMUTABLE" options is a
good one to specify though, keep that!

--
Sam http://samason.me.uk/