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
