Home | Previous Page | Next Page   Running a User-Defined Routine > Understanding Routine Resolution >

The Routine-Resolution Process

Routine resolution refers to the process that the database server uses when you invoke a routine. The database server also invokes routine resolution when another routine invokes a UDR. If the routine is overloaded, the query parser resolves the UDR from the system catalog tables, based on its routine signature. The parser performs any routine resolution necessary to determine which UDR to execute.

The Routine Signature

When a user or another routine invokes a routine, the database server searches for a routine signature that matches the routine name and arguments. If no exact match exists, the database server searches for a substitute routine, as follows:

  1. When several arguments are passed to a routine, the database server searches the sysprocedures system catalog table for a routine whose signature is an exact match for the invoked routine:
    1. The database server checks for a candidate routine that has the same data type as the leftmost argument.

      For more information, see Candidate List of Routines.

    2. If no exact match exists for the first argument, the database server searches the candidate list of routines using a precedence order of data types.

      For more information, see Precedence List of Data Types.

  2. The database server continues matching the arguments from left to right. If the database contains a routine with a matching signature, the database server executes this routine.

Important:
If one of the arguments for the routine is null, more than one routine might match the routine signature. If that situation occurs, the database server generates an error. For more information, see Null Arguments in Overloaded Routines .

Candidate List of Routines

The database server finds a list of candidate routines from the sysprocedures system catalog table that have the following characteristics:

If the candidate list does not contain a UDR with the same data type as an argument specified in the routine invocation, the database server checks for the existence of cast routines that can implicitly convert the argument to a data type of the parameter of the candidate routines.

For example, suppose you create the following two casts and two routines: 

CREATE IMPLICIT CAST (type1 AS type2)
CREATE IMPLICIT CAST (type2 AS type1)
CREATE FUNCTION g(type1, type1) ...
CREATE FUNCTION g(type2, type2) ...

Suppose you invoke function g with the following statement: 

EXECUTE FUNCTION g(a_type1, a_type2)

The database server considers both functions as candidates. The routine-resolution process selects the function g(type1, type1) because the leftmost argument is evaluated first. The database server executes the second cast, cast(type2 AS type1), to convert the second argument before the function g(type1, type1) executes.

For more information about casting, refer to Creating User-Defined Casts.

Tip:
Consider the order in which the database casts data and resolves routines as part of your decision to overload a routine.

Precedence List of Data Types

To determine which routine in the candidate list might be appropriate to an argument type, the database server builds a precedence list of data types for the argument. The routine-resolution process builds a precedence list, which is a partially ordered list of data types to match. It creates the precedence list as follows (from highest to lowest):

  1. The database server checks for a routine whose data type matches the argument passed to a routine.
  2. If the argument passed to the routine is a named row type that is a subtype in a type hierarchy, the database server checks up the type-hierarchy tree for a routine to execute.

    For more information, refer to Routine Resolution with User-Defined Data Types.

  3. If the argument passed to the routine is a distinct type, the database server checks the source data type for a routine to execute.

    If the source type is itself a distinct type, the database server checks the source type of that distinct type. For more information, refer to Routine Resolution with Distinct Data Types.

  4. If the argument passed to the routine is a built-in data type, the database server checks the candidate list for a data type in the built-in data type precedence list for the passed argument.

    For more information, refer to Precedence List for Built-In Data Types.

    If a match exists in this built-in data type precedence list, the database server searches for an implicit cast function.

  5. The database server adds implicit casts of the data types in steps 1 through 4 to the precedence list, in the order that the data types were added.
  6. If the argument passed to the routine is a collection type, the database server adds the generic type of the collection to the precedence list for the passed argument.
  7. The database server adds data types for which there are implicit casts between any data type currently on the precedence list (except the built-in data types) and some other data type.

If no qualifying routine exists, the database server returns the following error message: 

-674: Routine routine-name not found.

If the routine-resolution process locates more than one qualifying routine, the database server returns this error message: 

-9700: Routine routine-name cannot be resolved.

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. Table 4 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 4. Precedence of Built-In Data Types
Data Type Precedence List
CHAR VARCHAR, LVARCHAR
VARCHAR None
NCHAR NVARCHAR
NVARCHAR None
SMALLINT INT, INT8, DECIMAL, SMALLFLOAT, FLOAT
INT INT8, DECIMAL, SMALLFLOAT, FLOAT, SMALLINT
INT8 DECIMAL, SMALLFLOAT, FLOAT, INT, SMALLINT
SERIAL INT, INT8, DECIMAL, SMALLFLOAT, FLOAT, SMALLINT
SERIAL8 INT8, DECIMAL, SMALLFLOAT, FLOAT, INT, SMALLINT
DECIMAL SMALLFLOAT, FLOAT, INT8, INT, SMALLINT
SMALLFLOAT FLOAT, DECIMAL, INT8, INT, SMALLINT
FLOAT SMALLFLOAT, DECIMAL, INT8, INT, SMALLINT
MONEY DECIMAL, SMALLFLOAT, FLOAT, 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 Table 4 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);

Figure 2 shows the order in which the database server 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.

Figure 2. Example of Data Type Precedence During Routine Resolution
begin figure description - This figure is described in the surrounding text. - end figure description
Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]