Functions

PostgreSQL
Prev	Chapter 6. Type Conversion	Next

Functions

Function Evaluation

Check for an exact match in the pg_proc system catalog.
Look for the best match.
1. Make a list of all functions of the same name with the same number of arguments.
2. If only one function is in the list, use it if the input types can be coerced, and throw an error if the types cannot be coerced.
3. Keep all functions with the most explicit matches for types. Keep all if there are no explicit matches and move to the next step. If only one candidate remains, use it if the type can be coerced.
4. If any input arguments are "unknown", categorize the input candidate arguments as boolean, numeric, string, geometric, or user-defined. If there is a mix of categories, or more than one user-defined type, throw an error because the correct choice cannot be deduced without more clues. If only one category is present, then assign the "preferred type" to the input column which had been previously "unknown".
5. Choose the candidate with the most exact type matches, and which matches the "preferred type" for each column category from the previous step. If there is still more than one candidate, or if there are none, then throw an error.

Examples

Factorial Function

There is only one factorial 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)

Substring Function

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:

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

which is transformed by the parser to become

tgl=> select substr(text(varchar '1234'), 3);
 substr
--------
     34
(1 row)

Note: There are some heuristics in the parser to optimize the relationship between the char, varchar, and text types. For this case, substr is called directly with the varchar string rather than inserting an explicit conversion call.

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)

actually executes as

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

Prev	Home	Next
Operators	Up	Query Targets