Custom sorts

20 05 2009

Problem: You need to do a non natural sort on Postgres. For example, you may want your automotive results sorted Car, Truck, SUV then Motorcycle.

Solution: If we were using MySQL, (and we’re not) we’d do something like ORDER BY find_in_set(category, ‘Car, Truck, SUV, Motorcycle’). That lets you sort on the index position of category in the set. In Postgres, we’ll use arrays instead of sets.

Postgres already has a handy function (idx()) for getting the index position from an integer array. That function is part of the _int contrib module. Lets make one that works with text.

CREATE OR REPLACE FUNCTION idx(text[], text)
RETURNS int AS
$$
SELECT MIN(CASE WHEN $1[i] = $2 THEN i
ELSE NULL END)::int
FROM generate_series(array_lower($1, 1),
array_upper($1, 1)) i;
$$
LANGUAGE 'sql' IMMUTABLE STRICT;

Now lets use it:
ORDER BY idx(‘{Car,Truck,SUV,Motorcycle}’, category)

Advertisements

Actions

Information

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




%d bloggers like this: