Functions

Function Type Resolution

1. Select all potentially matching functions from the system catalog pg_proc. If a function name without schema qualification is used, the function is considered to be among those in the current search path. If a qualified function name is given, only functions in the specified schema are considered.

   If multiple functions with different parameter types are found in the search path, an appropriate function will be selected from among them.

2. Look for a function that exactly matches the input parameter types. If one is found, use it. If all input argument types are unknown, no matching function will be found.
3. If no exact match is found, check whether the function is a special type conversion function.
4. Look for the best match.
   1. Discard candidate functions whose input types do not match and cannot be implicitly converted to match. unknown text can be converted to anything in this case. If only one candidate remains, use it. Otherwise, proceed to the next step.
   2. Iterate through all candidate functions and retain those with the most accurate input type matches. Domains are treated as the same as their base types at this point. If no function matches exactly, retain all candidates. If only one candidate remains, use it. Otherwise, proceed to the next step.
   3. Iterate through all candidate functions and retain those that accept the most preferred types at positions requiring type conversion. If no function accepts a preferred type, retain all candidates. If only one candidate remains, use it. Otherwise, proceed to the next step.
   4. If any input parameter is of type unknown, check the type category of the corresponding parameter position in the remaining candidate functions. Use the string type at each position that can accept the string type category (this preference for strings is appropriate because unknown text is indeed string-like). Additionally, if all remaining candidate functions accept the same type category, select that type category. Otherwise, raise an error (since no correct choice can be made without further clues). Now discard candidate functions that do not accept the selected type category. Then, if any candidate function accepts a preferred type within that category, discard those that accept a non-preferred type at that parameter position. If no candidate survives these tests, retain all candidates. If only one candidate function qualifies, use it. Otherwise, proceed to the next step.
   5. If there are both unknown and known-type parameters, and all known-type parameters have the same type, assume the unknown parameters are also of that type. Check which candidate function can accept this type at the unknown parameter positions. If exactly one candidate qualifies, use it. Otherwise, raise an error.

Examples

Example 1: Round function parameter type resolution. There is only one round function with two parameters (the first is numeric, the second is integer). Therefore, the following query automatically converts the first parameter, which is of type integer, to numeric.

1
2
3
4
5

SELECT round(4, 4);
 round
--------
 4.0000
(1 row)

This is effectively transformed by the parser into:

1

SELECT round(CAST (4 AS numeric), 4);

Because numeric constants with decimal points are initially assigned the numeric type, the following query requires no type conversion and may be slightly more efficient:

1

SELECT round(4.0, 4);

Example 2: Substring function parameter type resolution. There are several substr functions, one of which accepts text and integer types. If it is called with an untyped string constant, the system will select the candidate function that accepts the preferred type of the string type category (i.e., the text type).

1
2
3
4
5

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

If the string is declared as type varchar, as in data retrieved from a table, the parser will attempt to convert it to text:

1
2
3
4
5

SELECT substr(varchar '1234', 3);
 substr
--------
     34
(1 row)

After parser transformation, this effectively becomes:

1

SELECT substr(CAST (varchar '1234' AS text), 3);

The parser learns from the pg_cast system catalog that text and varchar are binary-compatible, meaning one can be passed to a function accepting the other without any physical conversion. Therefore, in this case, no actual type conversion is performed.

Furthermore, if the function is called with an integer parameter, the parser will attempt to convert it to text:

1
2
3
4
5

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

After parser transformation, this effectively becomes:

1
2
3
4
5

SELECT substr(CAST (1234 AS text), 3);
 substr
--------
     34
(1 row)