Return a variant or nonvariant value

By default, a user-defined function is a variant function. A variant function has any of the following characteristics:
  • It returns different results when it is invoked with the same arguments.

    For example, a function whose return value is computed based on the current date or time is a variant function.

  • It has variant side effects, such as:
    • Modifying some database table, variable state, or external file
    • Failing to locate an external file, or a table or row in a database, and returning an error

You can explicitly specify a variant function with the VARIANT keyword. However, because a function is variant by default, this keyword is not required.

A nonvariant function always returns the same value when it receives the same argument, and it has none of the preceding variant side effects. Therefore, nonvariant functions cannot access external files or contain SQL statements, even if the SQL statements only SELECT static data and always return the same results. You specify a nonvariant function with the NOT VARIANT keywords.

You can create a functional index only on a nonvariant function. The return result for a functional index cannot contain a smart large object. Functional indexes are indexed on the value returned by the specified function rather than on the value of a column. The value returned by a functional index cannot contain a smart large object.

The database server can execute a nonvariant function during query compile time if all the arguments passed to it are constants. In that case, the result replaces the UDR expression in the query tree. This action by the database server is constant elimination. The database server cannot execute an SQL statement during constant elimination, thus a nonvariant function cannot execute even nonvariant SQL.

For information about creating a functional index, refer to the CREATE INDEX statement in the HCL OneDB™ Guide to SQL: Syntax.