Functions

Function Type Resolution

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

   If multiple functions with different parameter types are found in the search path, an appropriate function is chosen from them.

2. Look for a function that exactly matches the input parameter types. If one is found, use it. If the input parameters are all of the unknown type, no matching function is found.
3. If no exact match is found, check if the function is a special type conversion function.
4. Search for the optimal match.
   1. Discard candidate functions whose input types do not match and cannot be implicitly converted to match. unknown literals 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 precise input type matches. Here, domains are treated the same as their base types. If no function matches precisely, all candidates are kept. 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 type positions when type conversion is needed. If no function accepts the preferred type, all candidates are kept. If only one candidate remains, use it. Otherwise, proceed to the next step.
   4. If any input parameter is of the unknown type, examine the type category of the remaining candidate functions corresponding to the parameter position. At each position where the string type category is accepted, use the string type (this preference for strings is justified because unknown literals indeed resemble strings). Additionally, if all remaining candidates accept the same type category, choose that category. Otherwise, throw an error (since the correct choice cannot be determined without further information). Now eliminate candidates that do not accept the selected type category, and then remove candidates that accept a non-preferred type at that parameter position if any candidate accepts a preferred type within that category. If none of the candidates pass these tests, all candidates are kept. If only one candidate function satisfies the criteria, use it. Otherwise, proceed to the next step.
   5. If there are both unknown and known-type parameters, and all known-type parameters share the same type, assume the unknown parameters are also of that type, and verify which candidate function can accept this type at the unknown-parameter positions. If exactly one candidate fits, use it. Otherwise, an error occurs.

Examples

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

1
2
3
4
5

postgres=#SELECT round(4, 4);
 round
--------
 4.0000
(1 row)

It is actually transformed by the parser into:

1

postgres=#SELECT round(CAST (4 AS numeric), 4);

Since numeric constants with decimal points are initially assigned the numeric type, the following query does not require type conversion and might be slightly more efficient:

1

postgres=#SELECT round(4.0, 4);

Example 2: Substring function type resolution. There are several substr functions, one of which takes text and integer types. When called with an untyped string constant, the system chooses the candidate function accepting the preferred type (text type) within the string type category.

1
2
3
4
5

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

If the string is declared as varchar type, as if retrieved from a table, the parser attempts to convert it to text type:

1
2
3
4
5

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

Post-transformation by the parser, it essentially becomes:

1

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

The parser knows from the pg_cast catalog that text and varchar are binary-compatible, meaning one can be passed to a function expecting the other without any physical conversion. Hence, in this scenario, no actual type conversion occurs.

Moreover, if the function is called with an integer parameter, the parser tries to convert it to text type:

1
2
3
4
5

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

Post-transformation by the parser, it essentially becomes:

1
2
3
4
5

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