• An operator function implements a particular operator symbol.
• A built-in function is a predefined function that Universal Server provides for use in SQL statements.
• An aggregate function returns a single value for a set of retrieved rows.
• An end-user routine is a user-defined routine that end users can use in SQL statements.

• Arithmetic operators usually operate on numeric values.
• Text operators operate on character strings.
• Relational operators operate on expressions of numeric and string values.

The versions of the operator functions that Universal Server provides handle the built-in data types. You can write a new version of an operator function to change the functionality of an operator or to provide the operator on a data type that is not built into the database server. If you write a new version of an operator function, follow these rules:

• The name of the operator function must match the name that Figure 2-4 or Figure 2-5 lists. However, the name is case insensitive; the plus() function is the same as the Plus() function.
• The operator function must handle the correct number of parameters.
• The operator function must return the correct data type, where appropriate.

When an SQL statement contains an arithmetic operator, the database server automatically invokes the associated operator function. All operator functions in Figure 2-4 except positive() and negate() take two arguments; the positive() and negate() functions take only one argument. All these operator functions can return any data type.

Text Operators

When an SQL statement contains a text operator, the database server automatically invokes the associated operator function. The like() and matches() functions take two or three arguments and must return a Boolean value. The concat() function takes two arguments and can return any data type.

For information on syntax and use of the LIKE and MATCHES operators, see the description of the Condition segment in the Informix Guide to SQL: Syntax.

Relational Operators

When an SQL statement contains a relational operator, the database server automatically invokes the associated operator function. All operator functions in Figure 2-6 take two arguments and must return a Boolean value. For more information on relational operators, see the description of the Relational Operator segment in the Informix Guide to SQL: Syntax.

For end users to be able to use values of a new data type with relational operators, you must write new relational-operator functions that can handle the new data type. In these functions, you can:

• determine what the relational operators mean for that data type.

For example, you create the circle opaque type to implement a circle. A circle is a spatial object that does not have a single value to compare. However, you can define the relational operators on this data type can that use the value of its area: one circle is less than a second circle if its area is less than the area of the second. For more information on the circle opaque type, see "A Fixed-Length Opaque Type: circle".

• change from lexicographical sequence to some other ordering for a data type.

For example, suppose you create a data type, ScottishName, that holds Scottish names, and you want to order the data type in a different way than the U.S. English collating sequence. You might want the names McDonald and MacDonald to appear together on a phone list. You can define relational operators for this data type that equate the strings Mc and Mac. For more information, see "Changing the Sort Order".

The relational-operator functions are strategy functions for the built-in secondary access method, a generic B-tree. For more information, see "The Generic B-Tree Index".

Built-In Functions

Figure 2-4 needs to be reviewed!!

Figure 2-7
Built-In Functions That You Can Overload

(1 of 2)

Built-In Function	Number of Parameters	Parameter Types
abs()	1	real number
mod()	2	integer-number expression, integer-number expression
pow()	2	real-number expression, real-number expression
root()	1 or 2	real-number expression [, real-number expression]
round()	1 or 2	expression [, literal integer]
sqrt()	1	real-number expression
trunc()	1 or 2	expression [, literal integer]
exp(), log(), logn()	1	positive real-number expression
cos(), sin(), tan()	1	numeric expression
acos(), asin(), atan()	1	numeric expression
atan2()	2	numeric expression, numeric expression
hex()	1	integer expression
length(), char_length(), character_length(), octet_length()	1	character string
user()	0	None
current()	0	None
dbservername(), sitename()	0	None
today()	0	None
extend()	1	date/time expression

The following table lists the built-in functions you cannot overload.

cardinality()	month()
date()	trim()
day()	weekday()
dbinfo()	year()
mdy()

OL/Optical

The following table lists the built-in functions for the INFORMIX-OnLine/Optical product that you cannot overload.

descr()	volume()
family()

Universal Server provides versions of the built-in functions that handle the built-in data types. You can write a new version of a built-in function to allow the function to operate on your new opaque data type. If you write a new version of a built-in function, make sure you follow these rules:

• The name of the built-in function must match the name that Figure 2-7 lists. However, the name is case insensitive; the abs() function is the same as the Abs() function.
• The built-in function must be one that you can overload.
• The built-in function must handle the correct number of parameters, and these parameters must be the correct data type.
• The built-in function must return the correct data type, where appropriate.

Aggregate Functions

• COUNT returns the number of rows that satisfy the WHERE clause of a SELECT statement.
• AVG returns the average of all values that the query selects.
• MAX returns the largest value in the specified column or expression.
• MIN returns the smallest value in the specified column or expression.
• SUM returns the sum of all values in the specified column or expression.

End-User Routines

• An end-user function returns a value to the SQL statement.
• An end-user procedure performs some task but does not return a value to the SQL statement.