Postgresql – How to allow mixed integer and numeric parameters in icase

How to allow mixed integer and numeric parameters

Polymorphic types lead to a common, uniform data type. Anyelement will act as a placeholder for 4x the same type, and if you pass 3 different things (4th is the result type which will be adjusted to the 3 params), it’ll just give up.

Anycompatible won’t give up but rather attempt to convert the 3 different things to one common type, if it’s at all possible, and only if there’s only one unique way to convert them all. It will let you invoke the routine using a mix of types, but they’ll all end up converted.

Overload your function instead if you want more control (and plan to stay on v12):
_{demo at db<>fiddle}

CREATE OR REPLACE FUNCTION public.icase(--inn
   bool,int,bool,numeric,numeric)    
RETURNS numeric IMMUTABLE AS $body$
SELECT CASE WHEN $1 THEN $2 
            WHEN $3 THEN $4 
            ELSE $5 
       END 
$body$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION public.icase(--nin
   bool,numeric,bool,int,numeric)    
RETURNS numeric IMMUTABLE AS $body$
SELECT CASE WHEN $1 THEN $2 
            WHEN $3 THEN $4 
            ELSE $5 
       END 
$body$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION public.icase(--iin
   bool,int,bool,int,numeric)    
RETURNS numeric IMMUTABLE AS $body$
SELECT CASE WHEN $1 THEN $2 
            WHEN $3 THEN $4 
            ELSE $5 
       END 
$body$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION public.icase(--nnn
   bool,numeric,bool,numeric,numeric)    
RETURNS numeric IMMUTABLE AS $body$
SELECT CASE WHEN $1 THEN $2 
            WHEN $3 THEN $4 
            ELSE $5 
       END 
$body$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION public.icase(--iii
   bool,int,bool,int,int)    
RETURNS int IMMUTABLE AS $body$
SELECT CASE WHEN $1 THEN $2 
            WHEN $3 THEN $4 
            ELSE $5 
       END 
$body$ LANGUAGE sql;

With the 5 definitions above, any of the 8 possible combinations of int and numeric on any of the 3 positions gets resolved fine:

If you wanted to mix parameter types between different calls, not within a single invocation, your definition would already work as long as you used explicit, uniform type casts:
_{demo at db<>fiddle}

select icase(false, 0::numeric,false, 0.::numeric,0.::numeric);
select icase(false, 0::float4, false, 0.::float4, 0.::float4);
select icase(false, 0::float8, false, 0.::float8, 0.::float8);
select icase(false, 0::int2,   false, 0.::int2,   0.::int2);
select icase(false, 0::int4,   false, 0.::int4,   0.::int4);
select icase(false, 0::int8,   false, 0.::int8,   0.::int8);