Re: Division by zero

2009/8/2 Sam Mason <sam(at)samason(dot)me(dot)uk>:
> 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.
>

I thing, it's not true - RETURNS NULL ON NULL INPUT is equal to STRICT
flag, and it means, don't run function, when any param is null. For
optimalisator it means only one - when any parameter is constant NULL,
then function evaluation should be replaced by NULL. But not too much
often optimalizer should detect this case, so this is shortcut for
evaluator. This flag doesn't change inlining.

> 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!
>

There is paradox - IMMUTABLE function break inlinig :(. There is maybe bug
> --
>  Sam  http://samason.me.uk/
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>