Skip site navigation (1) Skip section navigation (2)

Re: Natural sort order

From: Richard Klingler <richard(at)klingler(dot)net>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: Natural sort order
Date: 2012-02-28 12:38:35
Message-ID: 20120228133835204823.bbf29d1f@klingler.net (view raw or flat)
Thread:
Lists: 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(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

In response to

Responses

pgsql-sql by date

Next:From: Igor NeymanDate: 2012-02-28 14:27:19
Subject: Re: SQL View to PostgreSQL View
Previous:From: Leif Biberg KristensenDate: 2012-02-28 12:22:56
Subject: Re: How to convert SQL store procedure to Postgresql function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group