Creating a functional index
You can build a functional index on a user-defined function. The user-defined function can be either an external function or an SPL function.
About this task
Procedure
To build a functional index on a user-defined function:
- Write the code for the user-defined function if it is an external function.
- Register the user-defined function in the database with the CREATE FUNCTION statement.
- Build the functional index with the CREATE INDEX statement.
Results
For example, to create a functional index on the darkness() function:
- Write the code for the user-defined darkness() function that operates on the data type and returns a decimal value.
- Register the user-defined function in the database with the CREATE
FUNCTION statement:
CREATE FUNCTION darkness(im image) RETURNS decimal EXTERNAL NAME '/lib/image.so' LANGUAGE C NOT VARIANT
In this example, you can use the default operator class for the functional index because the return value of the darkness() function is a built-in data type, DECIMAL.
- Build the functional index with the CREATE INDEX statement.
CREATE TABLE photos ( name char(20), picture image ... ); CREATE INDEX dark_ix ON photos (darkness(picture));
In this example, assume that the user-defined data type of image has already been defined in the database.
SELECT count(*) FROM photos WHERE
darkness(picture) > 0.5