From: Richard Klingler <richard@klingler.net>
To: pgsql-sql@postgresql.org
Subject: Re: Natural sort order
Date: Tue, 28 Feb 2012 13:38:35 +0100
Message-ID: <20120228133835204823.bbf29d1f@klingler.net> (raw)
In-Reply-To: <CAP_rwwkrnuANFjirZvkS=Ca2gejQvQa-xnM-vXsOFSQ1Jy92cQ@mail.gmail.com>
References: <20111217113306531983.1eb85a86@klingler.net>
<CAP_rwwkrnuANFjirZvkS=Ca2gejQvQa-xnM-vXsOFSQ1Jy92cQ@mail.gmail.com>
Took some time until I could try out this...
But as soon I want to create the fcuntion based index it tells me:
Error : ERROR: functions in index expression must be marked IMMUTABLE
Deleteing the sort function and recreating with the IMMUTABLE attribute gives the same error..
Here the functions:
CREATE FUNCTION btrsort_nextunit(text) RETURNS text AS $$
SELECT
CASE WHEN $1 ~ '^[^0-9]+' THEN
COALESCE( SUBSTR( $1, LENGTH(SUBSTRING($1 FROM '[^0-9]+'))+1 ), '' )
ELSE
COALESCE( SUBSTR( $1, LENGTH(SUBSTRING($1 FROM '[0-9]+'))+1 ), '' )
END
$$ LANGUAGE SQL
IMMUTABLE;
CREATE FUNCTION btrsort(text) RETURNS text AS $$
SELECT
CASE WHEN char_length($1)>0 THEN
CASE WHEN $1 ~ '^[^0-9]+' THEN
RPAD(SUBSTR(COALESCE(SUBSTRING($1 FROM '^[^0-9]+'), ''), 1, 12), 12, ' ') || btrsort(btrsort_nextunit($1))
ELSE
LPAD(SUBSTR(COALESCE(SUBSTRING($1 FROM '^[0-9]+'), ''), 1, 12), 12, ' ') || btrsort(btrsort_nextunit($1))
END
ELSE
$1
END
;
$$ LANGUAGE SQL
IMMUTABLE;
And the index creation:
create index port_name_btrsort_index on port(btrsort(name));
Which should speed up my query:
select * from port where name not like '%Z' order by btrsort(name) asc
cheers
richard
On Sat, 17 Dec 2011 16:16:07 +0100, Filip Rembiałkowski wrote:
> If you use btrsort(column) from the example, you can just create a
> functional index on this expression.
>
> CREATE INDEX mytable_column_btrsort_idx ON mytable( btrsort(column) );
>
> this can help.
>
>
>
>
>
> 2011/12/17 Richard Klingler <richard@klingler.net>:
>> Morning...
>>
>> What is the fastest way to achieve natural ordering from queries?
>>
>> I found a function at:
>> http://2kan.tumblr.com/post/361326656/postgres-natural-ordering
>>
>> But it increases the query time from around 0.4msecs to 74msecs...
>> Might be not much if occasional queries are made..but I use it for
>> building
>> up a hierarchical tree menu in a web application where every msecs
>> counts (o;
>>
>>
>> cheers
>> richard
>>
>>
>> --
>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: richard@klingler.net
Subject: Re: Natural sort order
In-Reply-To: <20120228133835204823.bbf29d1f@klingler.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox