Skip site navigation (1)
Skip section navigation (2)
## Re: Custom sorting

### In response to

### pgsql-novice by date

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

- Re: Custom sorting at 2009-05-16 19:16:03 from Tom Lane

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