1

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.

David Cournapeau
  • 78,318
  • 8
  • 63
  • 70
  • How about a custom operator? http://www.postgresql.org/docs/current/static/sql-createoperator.html – bma Jan 29 '14 at 03:27
  • I looked at that link but could not figure out whether I needed my own type to attach custom operators, or whether I could 'override' it on a per-column basis ? – David Cournapeau Jan 29 '14 at 03:36

1 Answers1

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).

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • This is correct, and the operator can be implemented with a function defined using whatever, include plpgsql. Another answer here goes into more detail: http://stackoverflow.com/questions/7205878/order-by-using-clause-in-postgresql – EricS Oct 29 '15 at 00:49