Does Order by support case? - Mailing list pgsql-general

Hi everyone,

I'm trying to sort email style subject lines and similar stuff.

When I try: (on 6.5.3)

select m_subject from wm_mails
where m_id > 900 and m_id < 1000
order by
(CASE WHEN (lower(m_subject) like 're: %')
THEN (substring(lower(m_subject) from 5))
ELSE
(lower(m_subject))
END
);

I get

Illegal ORDER BY node = 723

In this case I'm trying to ignore leading 're: ' strings in the sort.

I found that I can use something like
select m_subject, case when ... then ... else end as orderfield
etc order by orderfield

I also managed to do it by creating a custom function:
create function trimsub (text)
 returns text
as
'select CASE WHEN (lower($1) like \'re: %\')
 THEN (substring(lower($1) from 5))
 ELSE (lower($1))
END
'
language 'SQL'
;

But are there any other options where I can just put stuff in the "order
by" clause?

Thanks,
Link.