CREATE TYPE enum_type AS ENUM ('X-One','A-Two');
SELECT *
FROM (VALUES
('Not Enum'::text, 1::int, 'Uno'::text),
('Not Enum', 2, 'Dos'),
('Enum', 4, 'X-One'),
('Enum', 3, 'A-Two')) val (flag, id, val)
;
I need to write an ORDER BY clause that will result in the output of: 1, 2, 4, 3
Basically, if value is capable of being cast to the enum it should be and the sorting order of the enum used; otherwise order on the value of id.
It doesn't matter how the two groups, enums and non-enums, sort relative to each other - for any execution of the query either all values will be enum-able or none will be.
Trying to do this in pure SQL though I'm thinking I'll have to do this in pl/pgsql and put the "is enum-able" check external to the query and either use dynamic SQL or write two separate queries.
I tried casting the enum to an integer but it would not let me :(
Thanks in advanced for any thoughts.
David J.