Re: Natural sort order - Mailing list pgsql-sql

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