Order by using user defined values for a field

select
                *
from
                pet
order by
                field(species, 'cat', 'dog', 'bird') desc
 

Ref : http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks