Implement custom comparison in postgresql

Question

I have some data in a postgres table with one column called version (of type varchar). I would like to use my own comparison function to to order/sort on that column, but I am not sure what is the most appropriate answer:

• I have an JS implementation of the style comp(left, right) -> -1/0/1, but I don't know how I can use it in a sql order by clause (through plv8)
• I could write a C extension, but I am not particularly excited about this (mostly for maintenance reason, as writing the comparison in C would not be too difficult in itself)
• others ?

The type of comparisons I am interested are similar to version string ordering used in package managers.

Answer 1

You want:

ORDER BY mycolumn USING operator

See the docs for SELECT. It looks like you may need to define an operator for the function, and a b-tree operator class containing the operator to use it; you can't just write USING myfunc().

(No time to test this and write a demo right now).