Array Sorting
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).
) So, instead, we match pg_namespace.oid with pg_class.relnamespace.