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

From: "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Unusual sorting requirement (mixed enum/non-enum) - need thoughts
Date: 2015-07-03 15:27:29
Message-ID: CAKFQuwYhBAg5Y-2UUz_NL4-VHKumEAQQD-x=Vki32tpB2aPRfg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2015-07-03 15:36:00 Re: Unusual sorting requirement (mixed enum/non-enum) - need thoughts
Previous Message Adrian Klaver 2015-07-03 12:52:29 Re: max number of locks