PostgreSQL: Type mismatch exception catch over function/procedure arguments

Question

I want to catch an Postgres type mismatch exception (when provided function argument is not the type it should be by the arguments declaration) inside the function and rethrow it with my customized error response. How to obtain the described functionality? Maybe following exception is throwed by the function call and cannot be rethrowed. I tried to find the informations in Postgres documentation but without any result.

Answer 1

Overload it with polymorphic types and throw it there. It's not strictly catching and re-throwing, but since it'll only happen in the specific scenario where your exception would be thrown, it effectively does the same thing.

Here's PostgreSQL doc on function type resolution behaviour. Here's a demo at db<>fiddle:

create function f(arg1 int) returns int as $f$ select arg1 $f$ language sql;

select f(42), f('77');--one works as intended, one thanks to type coercion

f	f
42	77

select f('1'::text);--explicit type cast disables coercion

ERROR:  function f(text) does not exist
LINE 1: select f('1'::text);--explicit type cast disables coercion
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Overloading the function with variadic arg1 anycompatiblearray makes it a catch-all for calls that didn't match any other variant.

create function f(variadic args anycompatiblearray) returns int as $f$ 
begin raise exception 'MY CUSTOM MESSAGE: UNRECOGNIZED f() PARAMETER TYPES';
end $f$ language plpgsql;
 
select f('a'::text);

ERROR:  MY CUSTOM MESSAGE: UNRECOGNIZED f() PARAMETER TYPES
CONTEXT:  PL/pgSQL function f(anycompatiblearray) line 2 at RAISE

create table test as select 'a'::text as column1;
select f(column1) from test;

ERROR:  MY CUSTOM MESSAGE: UNRECOGNIZED f() PARAMETER TYPES
CONTEXT:  PL/pgSQL function f(anycompatiblearray) line 2 at RAISE

Still, that won't work with unknown literals:

select f('a');

ERROR:  function f(unknown) is not unique
LINE 2: select f('a');
               ^
HINT:  Could not choose a best candidate function. You might need to add explicit type casts.

And adding more overloads accepting specifically arguments of type unknown, won't cut it:

ERROR:  PL/pgSQL functions cannot accept type unknown
ERROR:  SQL functions cannot have arguments of type unknown

A workaround is to let the polymorphic catch-all be the only function under that name, wrapping calls to the other variants. That complicates the pass-through (you need to unpack the variadic and map it out to positional arguments) but thanks to the fact you effectively hijack any and all such calls, it also gives you more control over the order in which the db considers each function variant and how. This does catch and re-throw, so you'll have to catch the right one:

alter function f(int) rename to f_another_name;
create or replace function f(variadic args anycompatiblearray) returns int as $f$ 
begin return f_another_name(args[1]::int);--this pass-through can get complicated
exception when others then--should be more specific 
  raise exception 'MY CUSTOM MESSAGE: UNRECOGNIZED f() PARAMETER TYPES';
end $f$ language plpgsql;

--now even unknowns go there because there's just one f(), accepting all calls
select f('a');

ERROR:  MY CUSTOM MESSAGE: UNRECOGNIZED f() PARAMETER TYPES
CONTEXT:  PL/pgSQL function f(anycompatiblearray) line 4 at RAISE

select f(88),f('99');--regular calls still work thanks to the pass-through

f	f
88	99