Precedence list for built-in data types

If a routine invocation contains a data type that is not included in the candidate list of routines, the database server tries to find a candidate routine that has a parameter contained in the precedence list for the data type. The following table lists the precedence for the built-in data types when an argument in the routine invocation does not match the parameter in the candidate list.
Table 1. Precedence of built-in data types
Data type Precedence list
CHAR VARCHAR, LVARCHAR, IDSSECURITYLABEL
VARCHAR None
NCHAR NVARCHAR
NVARCHAR None
SMALLINT (SMINT) INT, SERIAL, BIGINT, BIGSERIAL, INT8, SERIAL8, DECIMAL, SMALLFLOAT, FLOAT
INT SERIAL, BIGINT, BIGSERIAL, INT8, SERIAL8, DECIMAL, SMALLFLOAT, FLOAT, SMALLINT
INT8 SERIAL8, BIGINT, BIGSERIAL, DECIMAL, SMALLFLOAT, FLOAT, INT, SERIAL, SMALLINT
BIGINT BIGSERIAL, INT8, SERIAL8, DECIMAL, SMALLFLOAT, FLOAT, INT, SERIAL, SMALLINT
SERIAL INT, BIGINT, BIGSERIAL, INT8, SERIAL8, DECIMAL, SMALLFLOAT, FLOAT, SMALLINT
SERIAL8 INT8, BIGINT, BIGSERIAL, DECIMAL, SMALLFLOAT, FLOAT, INT, SERIAL, SMALLINT
BIGSERIAL BIGINT, INT8, SERIAL8, DECIMAL, SMALLFLOAT, FLOAT, INT, SERIAL, SMALLINT
DECIMAL SMALLFLOAT, FLOAT, BIGINT, BIGSERIAL, INT8, INT, SMALLINT
SMALLFLOAT (SMFLOAT) FLOAT, DECIMAL, BIGINT, BIGSERIAL, INT8, INT, SMALLINT
FLOAT SMALLFLOAT, DECIMAL, BIGINT, BIGSERIAL, INT8, INT, SMALLINT
MONEY DECIMAL, SMALLFLOAT, FLOAT, BIGINT, BIGSERIAL, INT8, INT, SMALLINT
DATE None
DATETIME None
INTERVAL None
BYTE None
TEXT None
The following example shows overloaded test() functions and a query that invokes the test() function. This query invokes the function with a DECIMAL argument, test(2.0). Because a test() function for a DECIMAL argument does not exist, the routine-resolution process checks for the existence of a test() function for each data type that the precedence list in the previous table shows.
CREATE FUNCTION test(arg1 INT) RETURNING INT...
CREATE FUNCTION test(arg1 MONEY) RETURNING MONEY....

CREATE TABLE mytab (a real, ...
SELECT * FROM mytab WHERE a=test(2.0);
The database server then performs a search for the overloaded function, test(). The database server searches for a qualifying test() function that takes a single argument of type INTeger, in the following order:
  1. test(x SMALLFLOAT)
  2. test(x FLOAT)
  3. test(x BIGINT)
  4. test(x BIGSERIAL)
  5. test(x INT8)
  6. test(x SMALLINT)