PostgreSQL Server Programming - Second Edition

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) ...