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.
2 Answers
Answers 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.
Answers 2
You could do something like this
SELECT DISTINCT ON (interval_alias) *, to_timestamp(floor((extract('epoch' FROM index.created_at) / 10)) * 10) AT TIME ZONE 'UTC' AS interval_alias FROM index WHERE index.created_at >= '{start_date}' AND index.created_at <= '{end_date}' AND product = '{product_id}' GROUP BY id, interval_alias ORDER BY interval_alias; Firstly you define the parameter that will be your ordering column with AS. It could be function or any SQL expression. Then set it to ORDER BY expression and you're done!
In my opinion, this is the smoothest way to do such an ordering.
0 comments:
Post a Comment