There are two mechanisms for creating user-defined functions in Sybase IQ. You can use the SQL language to write the function, or you can use Java.
User-defined functions are processed by Adaptive Server
Anywhere. They do not take advantage of the performance features
of Sybase IQ. Queries that include user-defined functions run at
least 10 times slower than queries without them.
In very few cases, differences in semantics between ASA and Sybase IQ can produce different results for a query if it is issued in a user-defined function. For example, Sybase IQ treats the CHAR and VARCHAR data types as distinct and different, while ASA treats CHAR data as if it were VARCHAR.
You can implement your own functions in SQL using the CREATE FUNCTION statement. The RETURN statement inside the CREATE FUNCTION statement determines the data type of the function.
Once you have created a SQL user-defined function, you can use it anywhere a built-in function of the same data type is used.
Avoid using the CONTAINS predicate in a view that has
a user-defined function, because the CONTAINS criteria is ignored.
Use the LIKE predicate instead, or issue the query outside of a
view.
For more information on creating SQL functions, see Chapter 8, “Using Procedures and Batches” in the Sybase IQ System Administration Guide.
Although SQL functions are useful, Java classes provide a more powerful and flexible way of implementing user-defined functions, with the additional advantage that you can move them from the database server to a client application if desired.
Any class method of an installed Java class can be used as a user-defined function anywhere a built-in function of the same data type is used.
Instance methods are tied to particular instances of a class, and so have different behavior from standard user-defined functions.
For more information on creating Java classes, and on class methods, see “A Java Seminar” in the chapter “Introduction to Java in the Database” in the Adaptive Server Anywhere Programming Guide.
