Re: Conditional ordering operators

You should start a project for this on pgFoundry. It looks very useful!

On Feb 6, 2008, at 1:15 PM, Sergey Konoplev wrote:

> Hello everybody.
>
> I've written a script (see attachment) which creates operators
>
> @< - ascending ordering
> @> - descending ordering
>
> that allows you to replace code like this
>
> if <condition1> then
> for
> select <fields>
> from <tables>
> where <restrictions>
> order by
> field1 desc,
> field2
> loop
> <actions>
> end loop;
> elsif <condition2> then
> for
> select <fields>
> from <tables>
> where <restrictions>
> order by
> field3,
> field1 desc,
> field2 desc
> loop
> <actions>
> end loop;
> else
> for
> select <fields>
> from <tables>
> where <restrictions>
> order by
> field4
> loop
> <actions>
> end loop;
> end if;
>
> that way
>
> for
> select <fields>
> from <tables>
> where <restrictions>
> order by
> case when <condition1> then
> @>field1
> @<field2
> when <condition2> then
> @<field3
> @>field1
> @>field2
> else
> @<field4
> end
> loop
> <actions>
> end loop;
>
> It looks better, doesn't it?
>
> Also it provides Oracle like OVER PARTITION effect
>
> select * from (
> values
> (1.2, '2007-11-23 12:00'::timestamp, true),
> (1.4, '2007-11-23 12:00'::timestamp, true),
> (1.2, '2007-11-23 12:00'::timestamp, false),
> (1.4, '2007-01-23 12:00'::timestamp, false),
> (3.5, '2007-08-31 13:35'::timestamp, false)
> ) _
> order by
> @<column1 ||
> case
> when column1 = 1.2 then @<column3
> when column1 = 1.4 then @>column3
> else
> @>column2
> @<column3
> end;
>
> column1 | column2 | column3
> ---------+---------------------+---------
> 1.2 | 2007-11-23 12:00:00 | f
> 1.2 | 2007-11-23 12:00:00 | t
> 1.4 | 2007-11-23 12:00:00 | t
> 1.4 | 2007-01-23 12:00:00 | f
> 3.5 | 2007-08-31 13:35:00 | f
> (5 rows)
>
> Notice that rows 1-2 and 3-4 have opposite order in third column.
>
> p.s. Unfortunately I haven't manage yet with text fields because of
> localization.
>
> --
> Regards,
> Sergey Konoplev<conditional_ordering.sql>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings

--
Decibel!, aka Jim C. Nasby, Database Architect decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828