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

Re: Custom sorting

From: Jana <jana(dot)vasseru(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Custom sorting
Date: 2009-05-21 22:53:25
Message-ID: op.uuaxvbaog6o41l@truhlik (view raw or flat)
Thread:
Lists: pgsql-novice
On Tue, 19 May 2009 14:00:49 +0200, Jasen Betts <jasen(at)xnet(dot)co(dot)nz> wrote:

> 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.
>
>

Thank you (and Tom Lane) for advices. Your point about the sort order is
very valid, the data howerver should not create such situtation (however
this is not 100%). I was trying to get into the custom data types and that
stuff, and then i relazid that it is still no good, because it is very
hard for the user to interpret theese values - i mean - if i (as a user)
see that
    {1,111,33,2,9) > {1,222,44,3,8} i still have no idea why.
Well then i came with an idea that i could define another column, which is
computed as: a number of values which (when compared to this value) are
worse than this value. Which pretty much solves all the problems ;)

Regards,
     Jana



In response to

pgsql-novice by date

Next:From: Mehrotra, Abhinav (GE Healthcare)Date: 2009-05-22 09:06:24
Subject: RETURNING in stored procedure
Previous:From: Thomas KellererDate: 2009-05-21 11:40:00
Subject: Re: Unable to query on existing table - ERROR: relation &quot;zed&quot; does not exist

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