ORDER BY depending on column type

Question

My table looks like this:

a | b ----- 1 | a 2 | b 

In my query, I want to change the order by clause depending on the type of column.

So something like

 get_data($order_by_column) { .... ORDER BY CASE WHEN is_numeric($order_by_column) THEN $order_by_column ELSE CHAR_LENGTH($order_by_column) END } 

I checked, and it is quite difficult to determine the type of the column dynamically, as in the above query. What would be the alternative (or alternatives) to achieve this?

Answer 1

You may be interested in pg_typeof() :

 ORDER BY CASE pg_typeof($order_by_column) WHEN 'integer'::regtype THEN $order_by_column -- WHEN 'text'::regtype THEN ... -- WHEN 'boolean'::regtype THEN ... ELSE length($order_by_column) END 

Note that the legs of the CASE statement must return the appropriate types, which happens here, since $order_by_column and length($order_by_column) return an integer .

Learn more about types of object identifiers such as regtype in the manual .

Find all registered types:

 SELECT * from pg_type 

Again, more about pg_type in the manual .