Ordering Results by a Supplied Order

Greetings,

Often times I find it necessary to work with table rows in a specific, generally user-supplied order. It could be anything really that requires an ordering that can't come from a natural column. Most of the time this involved manipulating a position column from the client application. In any case, I've often found that to be cumbersome, but I think I've come up with a solution that some of you may find useful.

Regards,
Michael

-- Lets start by creating a new table.

CREATE TABLE my_items (
"rid" serial NOT NULL,
"position" int4 NOT NULL,
"name" text NOT NULL,
PRIMARY KEY ("rid")
);

INSERT INTO my_items (position, name) VALUES (1, 'Apple');
INSERT INTO my_items (position, name) VALUES (2, 'Orange');
INSERT INTO my_items (position, name) VALUES (3, 'Pear');

select * from my_items;

+-----+----------+--------+
| rid | position | name |
+-----+----------+--------+
| 1 | 1 | Apple |
| 2 | 2 | Orange |
| 3 | 3 | Pear |
+-----+----------+--------+

-- Now, lets return the results ordered by the our user-supplied order
-- ARRAY[3,2,1] is the key here where 3,2,1 represent the pk's in the order we wish

with x as (
select position, (ARRAY[3,2,1])[position] rid from generate_series(1, 3) as series(position) order by position asc
)
select item.* from x join my_items item on (item.rid=x.rid);
+-----+----------+--------+
| rid | position | name |
+-----+----------+--------+
| 3 | 3 | Pear |
| 2 | 2 | Orange |
| 1 | 1 | Apple |
+-----+----------+--------+

-- Better yet, lets create a function that will update the position column.

CREATE FUNCTION "public"."set_item_order"(IN items _int4) RETURNS "bool" AS
$BODY$
with x as(
select
position,
($1)[position] as rid
from generate_series(1,array_length($1, 1)) as series(position)
order by position asc
)
update my_items set position=x.position FROM x where x.rid=my_items.rid;
select TRUE;
$BODY$
LANGUAGE sql
COST 100
CALLED ON NULL INPUT
SECURITY INVOKER
VOLATILE;

select set_item_order(ARRAY[3,2,1]);

select * from my_items order by position;
+-----+----------+--------+
| rid | position | name |
+-----+----------+--------+
| 3 | 1 | Pear |
| 2 | 2 | Orange |
| 1 | 3 | Apple |
+-----+----------+--------+