Are you able to use a custom Postgres comparison function for ORDER BY clauses?

Question

In Python, I can write a sort comparison function which returns an item in the set {-1, 0, 1} and pass it to a sort function like so:

sorted(["some","data","with","a","nonconventional","sort"], custom_function) 

This code will sort the sequence according to the collation order I define in the function.

Can I do the equivalent in Postgres?

e.g.

SELECT widget FROM items ORDER BY custom_function(widget) 

Edit: Examples and/or pointers to documentation are welcome.

Answer 1

Yes you can, you can even create an functional index to speed up the sorting.

Edit: Simple example:

CREATE TABLE foo(     id serial primary key,     bar int ); -- create some data INSERT INTO foo(bar) SELECT i FROM generate_series(50,70) i; -- show the result SELECT * FROM foo;  CREATE OR REPLACE FUNCTION my_sort(int) RETURNS int  LANGUAGE sql  AS $$     SELECT $1 % 5; -- get the modulo (remainder) $$; -- lets sort! SELECT *, my_sort(bar) FROM foo ORDER BY my_sort(bar) ASC;  -- make an index as well: CREATE INDEX idx_my_sort ON foo ((my_sort(bar))); 

The manual is full of examples how to use your own functions, just start playing with it.

• SQL: http://www.postgresql.org/docs/current/static/xfunc-sql.html
• PL/pgSQL: http://www.postgresql.org/docs/current/static/plpgsql.html