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