Re: Unusual sorting requirement (mixed enum/non-enum) - need thoughts

["David G. Johnston" <david.g.johnston@xxxxxxxxx>]

On Fri, Jul 3, 2015 at 11:27 AM, David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:

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.

​Running 9.3 and discovered the pg_enum view that has the needed column that can be converted to integer (well, numeric since it can be fractional...)

Trying to figure out how best to use it...

David J.