The specific function to be used in a function invocation is determined according to the following steps.
Example 10-4. Rounding Function Argument Type Resolution
There is only one round
function with two arguments. (The first is numeric, the second is integer.) So the following query automatically converts the first argument of type integer to numeric:
SELECT round(4, 4);
round
--------
4.0000
(1 row)
That query is actually transformed by the parser to
SELECT round(CAST (4 AS numeric), 4);
Since numeric constants with decimal points are initially assigned the type numeric, the following query will require no type conversion and may therefore be slightly more efficient:
SELECT round(4.0, 4);
Example 10-5. Substring Function Type Resolution
There are several substr
functions, one of which takes types text and integer. If called with a string constant of unspecified type, the system chooses the candidate function that accepts an argument of the preferred category string (namely of type text).
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 convert it to become text:
SELECT substr(varchar '1234', 3);
substr
--------
34
(1 row)
This is transformed by the parser to effectively become
SELECT substr(CAST (varchar '1234' AS text), 3);
Note: The parser learns from the pg_cast catalog 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 argument of type integer, the parser will try to convert that to text:
SELECT substr(1234, 3);
substr
--------
34
(1 row)
This actually executes as
SELECT substr(CAST (1234 AS text), 3);
This automatic transformation can succeed because there is an implicitly invocable cast from integer to text.