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

On Fri, Jul 3, 2015 at 11:27 AM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> 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.