I've been wanting to add a safe division function for some time to help out team members who don't spend as much time writing SQL queries. I've just noticed that PG 13 seems to add an anycompatible pseudo-type. I've tried it out, and this seems to work as hoped and expected. I've not used the pseudo-types before, and I'm wondering if I'm stepping into something with risks and restrictions I might not be anticipating. Does anyone have some caveats to offer?
The function code is listed below, along with a quick select to illustrate its behavior in a few situations.
CREATE OR REPLACE FUNCTION tools.div_safe(
numerator anycompatible,
denominator anycompatible)
RETURNS real
AS $BODY$
SELECT numerator/NULLIF(denominator,0)::real
$BODY$
LANGUAGE sql;
COMMENT ON FUNCTION tools.div_safe (anycompatible, anycompatible) IS
'Pass in any two numbers that are, or can be converted to, numbers, and get a safe division real result.';
ALTER FUNCTION tools.div_safe (anycompatible, anycompatible)
OWNER TO user_bender;
Sample select and output:
-- (real, int))
select '5.1/nullif(null,0)', 5.1/nullif(null,0) as result union all
select 'div_safe(5.1,0)', div_safe(5.1, 0) as result union all
-- (0, 0)
select '0/nullif(0,0)', 5.1/nullif(null,0) as result union all
select 'div_safe(0, 0)', div_safe(0, 0) as result union all
-- (int, int)
select '5/nullif(8,0)::real', 5/nullif(8,0)::real as result union all
select 'div_safe(5,8)', div_safe(5, 8) as result union all
-- (string, int)
select 'div_safe(''5'',8)', div_safe('5', 8) as result union all
select 'div_safe(''8'',5)', div_safe('8', 5) as result union all
-- Rounding: Have to convert real result to numeric to pass it into ROUND (numeric, integer)
select 'round(div_safe(10,3)::numeric, 2)',
round(div_safe(10,3)::numeric, 2) as result
+-----------------------------------+-------------------+
| ?column? | result |
+-----------------------------------+-------------------+
| 5.1/nullif(null,0) | NULL |
| div_safe(5.1,0) | NULL |
| 0/nullif(0,0) | NULL |
| div_safe(0, 0) | NULL |
| 5/nullif(8,0)::real | 0.625 |
| div_safe(5,8) | 0.625 |
| div_safe('5',8) | 0.625 |
| div_safe('8',5) | 1.600000023841858 |
| round(div_safe(10,3)::numeric, 2) | 3.33 |
+-----------------------------------+-------------------+
I see no problem with your usage. You could just as well use anyelement in this case, which would work with older releases.
Your function is good if you always want real as a result. If you want the result to have the same type as the arguments, use a pseudo-type for the result as well.