Custom sorting

From: Jana <jana(dot)vasseru(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Custom sorting
Date: 2009-05-16 17:54:34
Message-ID: op.ut1aj9idkf3u59@truhlik
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hi,

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}
Sample comparison pseudocode:
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

Values above sorted in descending order:
{54.1;342}
{43.2;563}
{23.4;433;33.5;12}
{23.4;324;54.3;12.3;45}
{23.4;123;54.4;43.5;43}
{23.4;53;64.4;53.5;23}
{23.4;433;33.5;12.2}

I would like to make this ordering on database side since there are two
appliactions which use the data and both would have to reimplement the
ordering mechanism. I read several articles on custom sorting, which show
that i can use any function in the ORDER BY clause, however all the
examples assume only one input value from which the custom function
genereates some sort of automatically comparable value. However this
always involves only a single field - which i cannot use since i need two
rows to say which one is "higher" and "lower" - as the example tries to
show the absolute value returned by such comparison function may vary a
lot and thus is unusuable in ordering, but it can be used to determine
which one of two rows should be higher. How is it possible (if it is
possible ;) to do it? Thanks for help.

Regards,
Jana

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2009-05-16 19:16:03 Re: Custom sorting
Previous Message Dante torio 2009-05-15 12:33:48 Re: Primary/Foreign Keys