typeconv-func

The specific function referenced by a function call is determined using the following procedure.

Function type resolution

1. Select the functions to be considered from the pg_proc system catalog. If a non-schema-qualified function name is used, the functions considered are those that are visible in the current search path and have a matching name and number of parameters. If a qualified function name is given, only functions in the specified schema are considered.

1. If the search path finds multiple functions with identical parameter types, only the one that appears earliest in the search path is considered. Functions with different parameter types are treated equally, regardless of their position in the search path.

2. If a function is declared with a VARIADIC array parameter and the call does not use the VARIADIC keyword, the function is treated as if its array parameter were replaced by one or more occurrences of its element type, as needed to match the call. After this expansion, the function might have the same parameter types as a non-variadic function. In this case, the function that appears earlier in the search path is used. If the two functions are in the same schema, the non-variadic one is preferred.Calling a variadic function found in a schema that allows untrusted users to create objects can lead to a security hazard ^[1]. A malicious user can take control and execute arbitrary SQL functions as if you were executing them. To bypass this hazard, replace the call with one that uses the VARIADIC keyword. Calls that involve a VARIADIC "any" parameter usually do not have an equivalent form that includes the VARIADIC keyword. To make those calls securely, the function's schema must only allow trusted users to create objects.

3. Consider functions with default parameter values to match any call that omits zero or more defaultable parameter positions. If more than one such function matches a call, use the one that appears earliest in the search path. If there are two or more such functions in the same schema with the same non-default parameters—which is possible if they have different sets of default parameters—the system cannot determine which one to select. This results in an "ambiguous function call" error if no better match for the call can be found.Calling any function found in a schema that allows untrusted users to create objects can lead to an availability hazard ^[1]. A malicious user can create a function with the name of an existing function, copy its parameters, and append new parameters with default values. This can prevent new calls to the original function. To prevent this hazard, place functions in schemas where only trusted users can create objects.

2. Check for a function that accepts the exact input parameter types. If one exists—and there can be only one exact match among the set of functions considered—use it. The lack of an exact match can lead to a security hazard when calling a function found in a schema that allows untrusted users to create objects ^[1]. In such cases, cast the parameters to force an exact match. Cases involving unknown will never find a match at this step.

3. If no exact match is found, see if the function call appears to be a specific type conversion request. This occurs if the function call has only one parameter and the function name matches the internal name of some data type. Additionally, the function parameter must be a literal of unknown type, a type that is binary-coercible to the named data type, or a type that can be converted to the named data type by applying its I/O functions. This means the conversion is to or from a standard string type. When these conditions are met, the function call is treated as a form of CAST specification. ^[2]

4. Look for the best match.

1. Discard candidate functions whose input types do not match and cannot be converted to match using an implicit conversion. For this purpose, unknown literals are assumed to be convertible to anything. If only one candidate remains, use it. Otherwise, continue to the next step.

2. If any input parameter is a domain type, treat it as the domain's base type for all subsequent steps. This ensures that domains behave like their base types for ambiguous operator resolution.

3. Traverse all candidate functions and keep those with the most exact matches to the input types. Keep all candidates if none have exact matches. If only one candidate remains, use it. Otherwise, continue to the next step.

4. Traverse all candidate functions and keep those that accept preferred types of the input data type's type category at the most positions where type conversion is required. Keep all candidates if none accept preferred types. If only one candidate remains, use it. Otherwise, continue to the next step.

5. If any input parameters are unknown, check the type categories accepted by the remaining candidates at those parameter positions. At each position, select the string category if any candidate accepts that category. This bias toward string is appropriate because an unknown-type literal resembles a string. Otherwise, if all remaining candidates accept the same type category, select that category. Otherwise, the process fails because the correct choice cannot be deduced without additional clues. Now, discard candidates that do not accept the selected type category. Also, if any candidate accepts a preferred type in that category, discard candidates that accept non-preferred types for that parameter. Keep all candidates if no candidate passes these tests. If only one candidate remains, use it. Otherwise, continue to the next step.

6. If there are both unknown and known-type parameters, and all the known-type parameters have the same type, assume the unknown parameters are also of that type. Then check which candidate functions can accept that type at the unknown parameter positions. If exactly one candidate passes this test, use it. Otherwise, the process fails.

Note that the "best match" rules are the same for both operator and function type resolution. The following are some examples.

Example 1: Rounding function parameter type resolution

There is only one round function that takes two parameters: a numeric parameter and an integer parameter. Therefore, the following query automatically converts the first integer parameter to numeric:

    SELECT round(4, 4);

     round
    --------
     4.0000
    (1 row)

The parser actually converts this query to:

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

Because a numeric constant that contains a decimal point is initially assigned the type numeric, the following query does not require a type conversion and might be slightly more efficient:

    SELECT round(4.0, 4);

Example 2: Variadic function resolution

    CREATE FUNCTION public.variadic_example(VARIADIC numeric[]) RETURNS int
      LANGUAGE sql AS 'SELECT 1';
    CREATE FUNCTION

This function accepts, but does not require, the VARIADIC keyword. It can handle both integer and numeric parameters:

    SELECT public.variadic_example(0),
           public.variadic_example(0.0),
           public.variadic_example(VARIADIC array[0.0]);
     variadic_example | variadic_example | variadic_example
    ------------------+------------------+------------------
                    1 |                1 |                1
    (1 row)

However, the first and second calls will prefer a more specific function if available:

    CREATE FUNCTION public.variadic_example(numeric) RETURNS int
      LANGUAGE sql AS 'SELECT 2';
    CREATE FUNCTION

    CREATE FUNCTION public.variadic_example(int) RETURNS int
      LANGUAGE sql AS 'SELECT 3';
    CREATE FUNCTION

    SELECT public.variadic_example(0),
           public.variadic_example(0.0),
           public.variadic_example(VARIADIC array[0.0]);
     variadic_example | variadic_example | variadic_example
    ------------------+------------------+------------------
                    3 |                2 |                1
    (1 row)

If given the default configuration and only the first function exists, the first and second calls are not secure. Any user can intercept them by creating the second or third function. The third call is secure because it matches the parameter type exactly and uses the VARIADIC keyword.

Example 3: Substring function type resolution

There are several substr functions, including one that takes text and integer types. If substr is called with a character constant of an unspecified type, the system chooses the candidate function that accepts a parameter from the preferred string category, which is the text type.

    SELECT substr('1234', 3);

     substr
    --------
         34
    (1 row)

If the string is declared as type varchar, as it would be if it came from a table, the parser will try to convert it to text:

    SELECT substr(varchar '1234', 3);

     substr
    --------
         34
    (1 row)

The conversion made by the parser is:

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

Also, if the function is called with a parameter of type integer, the parser will try to convert it to text:

    SELECT substr(1234, 3);
    ERROR:  function substr(integer, integer) does not exist
    HINT:  No function matches the given name and argument types. You might need
    to add explicit type casts.

This will not work because the integer type has no implicit cast to text. However, an explicit cast will work:

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

     substr
    --------
         34
    (1 row)

^[1] This hazard does not occur for non-schema-qualified names, because a search path that includes schemas allowing untrusted users to create objects is not a secure schema usage pattern.

^[2] The reason for this step is to support function-style cast specifications without a real cast function. If a cast function exists, it is conventionally named after its output type and does not require a special case. For more information, see CREATE CAST.