Re: Natural sort order

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(at)klingler(dot)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(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql