Creatinf icase function using
CREATE OR REPLACE FUNCTION public.icase(
cond1 boolean, res1 anyelement,
cond2 boolean, res2 anyelement,
conddefault anyelement)
RETURNS anyelement LANGUAGE 'sql' IMMUTABLE PARALLEL UNSAFE AS $BODY$
SELECT CASE WHEN $1 THEN $2 WHEN $3 THEN $4 ELSE $5 END;
$BODY$;
select
icase( false, 0, false, 0.,0.)
throws error
function icase(boolean, integer, boolean, numeric, numeric) does not exist
How to allow icase to accept mixed integer/numeric parameters?
Per the link:
https://www.postgresql.org/docs/current/xfunc-sql.html#XFUNC-SQL-POLYMORPHIC-FUNCTIONS
With make_array declared as above, you must provide two arguments that are of exactly the same data type; the system will not attempt to resolve any type differences.
An alternative approach is to use the “common” family of polymorphic types, which allows the system to try to identify a suitable common type.
So:
CREATE OR REPLACE FUNCTION public.icase(cond1 boolean, res1 anycompatible, cond2 boolean, res2 anycompatible, conddefault anycompatible)
RETURNS anycompatible
LANGUAGE sql
IMMUTABLE
AS $function$
SELECT CASE WHEN $1 THEN $2 WHEN $3 THEN $4 ELSE $5 END;
$function$
;
Which when run does:
select icase(false, 0, false, 0.,0.);
icase
-------
0
select icase(false, 0, false, 0.,0.0);
icase
-------
0.0
Though as you see it will coerce the output to a common suitable type.