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

Re: Custom sorting

From: Jasen Betts <jasen(at)xnet(dot)co(dot)nz>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Custom sorting
Date: 2009-05-19 12:00:49
Message-ID: guu71h$kli$1@reversiblemaps.ath.cx (view raw or flat)
Thread:
Lists: pgsql-novice
On 2009-05-16, Jana <jana(dot)vasseru(at)gmail(dot)com> wrote:
> i'm looking for a way to create a custom sort table. The table has a
> column which is currently character varying (255), although i might do
> with an array of double if it will help. This column can contain various
> data as it represents an output of a polymorfic algorithm, this is usualy
> a sequence of numbers which have to be compared with some pretty complex
> rules.
> Example values:
> {23.4;324;54.3;12.3}
> {23.4;53;64.4;53.5}
> {23.4;123;54.4;43.5}
> {43.2;563}
> {54.1;342}
> {23.4;433;33.5}
>
> {A;B;C;D;E}

> function (R1, R2)
> if (R1.A == R2.A)
> 	if (R1.B > 200) and (R2.B > 200)
> 		if (R1.B > 1000) and (R2.B > 1000)
> 			return R1.D - R2.D;
> 		else
> 			return R2.C - R1.C;
> 	else
> 		if (R1.C < 50) and (R2.C < 50)
> 			return R1.E - R2.E;
> 		else
> 			return R1.D - R2.D;
> else
> 	return R1.A - R2.A

 to me this does not look like it defines an ordering

 in other words a>b and b>c does not imply a>c
 
 a {1,111,33,2,9)
 b {1,222,44,3,8}
 c {1,333,55,4,7}

 F(a,b)=  9-8  = 1      thus a>b 
 F(b,c)= 55-44 = 11     thus b>c
 F(a,c)=  2-3  = -1     thus c>a

What you ask is therfore impossible.


if you can create an ordering that is consistent  where a>b and b>c
does imply a>c you can then create an operator class for it

(I have no idea how to do this bit)

And then you can create a btree index using it.


In response to

pgsql-novice by date

Next:From: Jaromír KamlerDate: 2009-05-21 08:17:26
Subject: Unable to query on existing table - ERROR: relation &quot;zed&quot; does not exist
Previous:From: Hartman, MatthewDate: 2009-05-17 14:35:38
Subject: Installing PostgreSQL 8.3 and DBI-LINK on Windows.

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