Ordering Results by a Supplied Order

[Michael Sacket <msacket@xxxxxxxxxxxxxxx>]

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  |
+-----+----------+--------+