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/ -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
