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  then
>    for
>        select 
>        from 
>        where 
>        order by
>            field1 desc,
>            field2
>    loop
>        
>    end loop;
> elsif  then
>    for
>        select 
>        from 
>        where 
>        order by
>            field3,
>            field1 desc,
>            field2 desc
>    loop
>        
>    end loop;
> else
>    for
>        select 
>        from 
>        where 
>        order by
>            field4
>    loop
>        
>    end loop;
> end if;
>
> that way
>
> for
>    select 
>    from 
>    where 
>    order by
>        case when  then
>            @>field1
>            @        when  then
>            @            @>field1
>            @>field2
>        else
>            @        end
> loop
>    
> 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
>    @    case
>    when column1 = 1.2 then @    when column1 = 1.4 then @>column3
>    else
>        @>column2
>        @    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
> ---------------------------(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