Custom sorting - Mailing list pgsql-novice

From Jana
Subject Custom sorting
Date
Msg-id op.ut1aj9idkf3u59@truhlik
Whole thread Raw
Responses Re: Custom sorting
List 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

pgsql-novice by date:

Previous
From: Dante torio
Date:
Subject: Re: Primary/Foreign Keys
Next
From: Tom Lane
Date:
Subject: Re: Custom sorting