Can I write a custom function to handle different numeric types without multiple function definitions?

Question

As an example, suppose I'd like to write a custom function in postgres that safely divides two numbers -- i.e. it should check whether either of the arguments are null and whether the divisor is zero. It should handle these error conditions gracefully, and otherwise return the expected quotient.

The current code works well as long as the two arguments are of the same numeric type (e.g. both integers, both numeric, etc.)

CREATE OR REPLACE FUNCTION safe_divide(anyelement, anyelement) RETURNS numeric AS $$
  SELECT CASE 
     WHEN $1 IS NULL OR $2 IS NULL OR $2 = 0 THEN NULL 
     ELSE $1::numeric / $2::numeric
     END;
$$ LANGUAGE SQL;

My question: is there a way to write this function so that I can supply different numeric types?

I would like to avoid:

• Needing to explicitly cast the arguments when calling the function (e.g. safe_divide(x::numeric, y::numeric))

• Needing to define the function for every possible datatype

Thanks!

Answer 1

If you define your function with numeric parameters and double precision, then you can use it for every numeric parameters.

  CREATE OR REPLACE FUNCTION safe_divide(numeric, numeric) RETURNS numeric AS $$
   SELECT CASE 
     WHEN $1 IS NULL OR $2 IS NULL OR $2 = 0 THEN NULL ELSE $1 / $2 END;
  $$ LANGUAGE SQL;

  CREATE OR REPLACE FUNCTION safe_divide(double precision, double precision)
  RETURNS numeric AS $$
   SELECT CASE 
     WHEN $1 IS NULL OR $2 IS NULL OR $2 = 0 THEN NULL 
                                             ELSE $1::numeric / $2::numeric END;
  $$ LANGUAGE SQL;

It has no sense for other kind data types

 postgres=# select safe_divide(10::float,10::int);
       safe_divide       
 ------------------------
  1.00000000000000000000
 (1 row)

 postgres=# select safe_divide(10::numeric,10::int);
       safe_divide       
 ------------------------
  1.00000000000000000000
 (1 row)

 postgres=# select safe_divide(10::int,10::int);
       safe_divide       
 ------------------------
  1.00000000000000000000
 (1 row)

 postgres=# select safe_divide(10,10.0);
       safe_divide       
 ------------------------
  1.00000000000000000000
 (1 row)

This is typical pattern in PostgreSQL