sqlpostgresqlplpgsql

How to allow mixed integer and numeric parameters in icase

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?

Solution

  • 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.