A functional index is defined as an index that points to the result of a function. The function is a calculation that was applied to one or more columns of a single table. Functional indices allows the user to search quickly through the calculated results of a function.

Using the shipped_orders table, we can create a functional index which indices the months that the orders were shipped on. Use the extract() function to retrieve only the month from the date string. If we were to use a select to perform this operation, it will look like this:

  SELECT extract (month FROM ship_date) 
    FROM shipped_orders;   
   

The original dates were:

   2001-02-15
   2001-03-01
   2001-04-20
   

After having executed the above SELECT statement, the output was:

 date_part
-----------
         2
         3
         4
(3 rows)
   

This is only a small part of a large database filled with dates that we often perform searches for the months in which orders were shipped. If you have a similar situation, then it is a good idea to create a functional index. In order to create a functional index with the example above, use the following command:

Example 5-7. Creating a Simple Functional Index

  CREATE INDEX month_shipped_index 
      ON shipped_orders(extract(month FROM ship_date)); 
   

If you wanted to use more than one function to achieve a specific result and then have an index pointing to that result, then you can define a user-created function.

For instance, the customer table contains a list of customer identification numbers, their last name, and their first name. The customer first names all have a leading Mr, Mrs, or Dr, followed by a period. We want to drop the leading title and only display the names. If we were to perform a select statement, it outputs this:

  SELECT substr
           (
           firstname, strpos(firstname, '.')+1, 
           length(firstname)-strpos(firstname, '.')
           ) 
           FROM customer;
   

To create an index of first names without their titles will require creating a function and then creating an index which points to the result of that function. To create this type of function, insert the SQL statement into the body of a function statement. Using our previous example, we can create a function called dropTitle.

Example 5-8. Creating a Function

  CREATE FUNCTION dropTitle(text)
           RETURNS text AS
           'SELECT substr
           (
           firstname, strpos(firstname, \'.\')+1, 
           length(firstname)-strpos(firstname, \'.\')
           ) 
           FROM customer WHERE firstname =$1'
           LANGUAGE 'SQL';
   

This function uses another function called substring to search for the position of the period (.) in the string, then extract that period along with any string located to the left of the period (.). We need to use the backslash when quoting the periods because the system may think it is the end quote for the SQL SELECT statement if the backslash is omitted. For more information on functions, refer to the where functions and operators are explained in detail.

To create an index on the resulting column requires calling the dropTitle function. The command below creates a functional index on the results of the dropTitle function:

Example 5-9. Creating a Complex Functional Index

  CREATE INDEX non_titled_names_index
      ON customer(dropTitle(firstname)); 
   

Index Names

The functional index created with the name "non_titled_names_index" is long, but it describes to you what it is. When naming indices, you need to give it meaningful names. Don't worry about having to use the index names, because indices are used automatically by PostgreSQL whenever it thinks that the index is the faster route to returning a result for a query. The only time you are required to use the index name after creating it is during destruction of the index from the database.