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