Thread: Conditional ordering operators

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

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@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

On 2/12/08, Decibel! <decibel@decibel.org> wrote:
> 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
> >

Thank you for the advice. I've put it down in my organizer.

--
Regards,
Sergey Konoplev