The argument types of function calls are resolved according to
the following steps.

Function Argument Type Resolution

Check for an exact match in the pg_proc system catalog. (Cases involving
unknown will never find a match at
this step.)

If no exact match appears in the catalog, see whether
the function call appears to be a trivial type coercion
request. This happens if the function call has just one
argument and the function name is the same as the
(internal) name of some data type. Furthermore, the
function argument must be either an unknown-type literal or
a type that is binary-compatible with the named data type.
When these conditions are met, the function argument is
coerced to the named data type without any explicit
function call.

Look for the best match.

Make a list of all functions of the same name with
the same number of arguments for which the input types
match or can be coerced to match. (unknown literals are assumed to be
coercible to anything for this purpose.) If there is
only one, use it; else continue to the next step.

Run through all candidates and keep those with the
most exact matches on input types. Keep all candidates
if none have any exact matches. If only one candidate
remains, use it; else continue to the next step.

Run through all candidates and keep those with the
most exact or binary-compatible matches on input types.
Keep all candidates if none have any exact or
binary-compatible matches. If only one candidate
remains, use it; else continue to the next step.

Run through all candidates and keep those that
accept preferred types at the most positions where type
coercion will be required. Keep all candidates if none
accept preferred types. If only one candidate remains,
use it; else continue to the next step.

If any input arguments are unknown, check the type categories accepted
at those argument positions by the remaining
candidates. At each position, select the string category if any candidate accepts
that category (this bias towards string is appropriate
since an unknown-type literal does look like a string).
Otherwise, if all the remaining candidates accept the
same type category, select that category; otherwise
fail because the correct choice cannot be deduced
without more clues. Also note whether any of the
candidates accept a preferred data type within the
selected category. Now discard candidates that do not
accept the selected type category; furthermore, if any
candidate accepts a preferred type at a given argument
position, discard candidates that accept non-preferred
types for that argument.

If only one candidate remains, use it. If no
candidate or more than one candidate remains, then
fail.

Examples

Example 5-4. Factorial Function Argument Type
Resolution

There is only one int4fac
function defined in the pg_proc
catalog. So the following query automatically converts the
int2 argument to int4:

tgl=> SELECT int4fac(int2 '4');
int4fac
---------
24
(1 row)

and is actually transformed by the parser to

tgl=> SELECT int4fac(int4(int2 '4'));
int4fac
---------
24
(1 row)

Example 5-5. Substring Function Type
Resolution

There are two substr functions
declared in pg_proc. However,
only one takes two arguments, of types text and int4.

If called with a string constant of unspecified type, the
type is matched up directly with the only candidate function
type:

tgl=> SELECT substr('1234', 3);
substr
--------
34
(1 row)

If the string is declared to be of type varchar, as might be the case if it comes from a
table, then the parser will try to coerce it to become
text:

Note: Actually, the parser is aware that
text and varchar are binary-compatible, meaning that one can be
passed to a function that accepts the other without doing
any physical conversion. Therefore, no explicit type
conversion call is really inserted in this case.

And, if the function is called with an int4, the parser will try to convert that to
text:

tgl=> SELECT substr(1234, 3);
substr
--------
34
(1 row)

which actually executes as

tgl=> SELECT substr(text(1234), 3);
substr
--------
34
(1 row)

This succeeds because there is a conversion function
text(int4) in the system catalog.