Database Reference
In-Depth Information
Custom sort orders
The last example in this chapter is about using functions for different ways of sorting.
Say we are given a task of sorting words by their vowels only, and in addition to that,
make the last vowel the most significant one when sorting. While this task may seem
really complicated at first, it is easy to solve 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)
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 function
re-
versed_vowels(word)
is used in the
WHERE
clause or
ORDER BY
.
