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

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 (view raw or flat)
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

pgsql-novice by date

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

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