ORDER BY depending on the column type

Question

My table is as below:

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

In my query, I want to change the order by clause depending on the type of the 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've checked and it seems it's quite difficult to determine the column type dynamically like in the above query. What would be an alternative (or alternatives) to achieve this?

Answer 1

You might 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 a CASE statement need to return matching types, which happens to be the case here, since $order_by_column and length($order_by_column) both return integer.

More about object identifier types like regtype in the manual.

Find all registered types:

SELECT * from pg_type

Again, more about pg_type in the manual.