I have this little sorting problem that had been bothering me for a month, but as it’s only a minor annoyance, I hadn’t spared the cycles to work on it. Last Friday I decided I needed an instant gratification project, and set about solving it.

I have a table that contains hostnames, cards, and the slots on the host chassis those cards are installed in. All datatypes are varchars. The sorting problem arises because I have some cards that are actually installed on other cards; those are referenced by a slot/subslot designation.

Here’s some sample data:

testytest=# SELECT * FROM cards ORDER BY card_slot;
hostname | card_slot |   card_model
----------+-----------+----------------
zucchini | 0         | card
zucchini | 0/0       | daughter card
zucchini | 0/1       | daughter card
zucchini | 1         | card
zucchini | 1/0       | daughter card
zucchini | 1/1       | daughter card
zucchini | 1/15      | daughter card
zucchini | 1/2       | daughter card
zucchini | 17        | something else
zucchini | 18        | something else
zucchini | 2         | another card
zucchini | 3         | another card
zucchini | 4         | another card
(13 rows)

I’d like to see 1/15 come after 1/2, and 17 & 18 come after 2, 3, & 4. (Like I said, it’s a minor annoyance. But still an annoyance.) I need to be able to sort both pieces in numerical order. I went through some weird gymnastics writing functions to split out & return each piece, but each of my solutions introduced other problems. (These instant gratification projects so rarely are, eh.)

Before I wrapped things up for the day, I read the PostgreSQL docs about array functions & operators. That percolated around in my brain and a solution came to me while I was out hiking over the weekend:

First, I reCAST string_to_array & made it return a set of integers:
CREATE OR REPLACE function foo(varchar(15)) RETURNS integer[]
AS $$
SELECT CAST(string_to_array($1, '/') AS integer[])
$$
LANGUAGE SQL;

Since Pg includes < and > array functions, I expected to be able to sort by the array my function returned:

testytest=# SELECT hostname, card_slot, card_model, foo(card_slot) AS sort_value
FROM cards
ORDER BY sort_value;
hostname | card_slot |   card_model   | sort_value
----------+-----------+----------------+------------
zucchini | 0         | card           | {0}
zucchini | 0/0       | daughter card  | {0,0}
zucchini | 0/1       | daughter card  | {0,1}
zucchini | 1         | card           | {1}
zucchini | 1/0       | daughter card  | {1,0}
zucchini | 1/1       | daughter card  | {1,1}
zucchini | 1/2       | daughter card  | {1,2}
zucchini | 1/15      | daughter card  | {1,15}
zucchini | 2         | another card   | {2}
zucchini | 3         | another card   | {3}
zucchini | 4         | another card   | {4}
zucchini | 17        | something else | {17}
zucchini | 18        | something else | {18}
(13 rows)

Voila. Works as expected on my small (~2000 rows) data set. It also fails as expected when passed bad data (eg something with a text string).