Custom sort orders

The last example in this chapter, is about using functions for different ways of sorting.

Say we are given a task to sort words by their vowels only, and in addition to this, to make the last vowel the most significant one when sorting. While this task may seem really complicated at first, it can be easily solved with functions:

CREATE OR REPLACE FUNCTION reversed_vowels(word text) 
    RETURNS text AS $$
  vowels = [c for c in word.lower() if c in 'aeiou']
  vowels.reverse()
  return ''.join(vowels)
$$ LANGUAGE plpythonu IMMUTABLE;

postgres=# select word,reversed_vowels(word) from words order by reversed_vowels(word);
    word     | reversed_vowels
-------------+-----------------
 Abracadabra | aaaaa
 Great       | ae
 Barter      | ea
 Revolver    | eoe
(4 rows)

Note

Before performing this code, please make sure you have Python 2.x installed. We will discuss PL/Python in much detail in the later chapters of this book.

The best part is that you can use your new function in an index definition:

postgres=# CREATE INDEX reversed_vowels_index ON words (reversed_vowels(word));
CREATE INDEX

The system will automatically use this index whenever the reversed_vowels(word) function is used in the WHERE or ORDER BY clause.