Search Postgresql Archives

Conditional ordering operators

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

Attachment: conditional_ordering.sql
Description: Binary data

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux