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