Re: [GENERAL] Conditional ordering operators

2008-02-13 Thread Sergey Konoplev 
On 2/12/08, Decibel! [EMAIL PROTECTED] 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

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Conditional ordering operators

2008-02-11 Thread Decibel! 

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 Konoplevconditional_ordering.sql
---(end of  
broadcast)---

TIP 5: don't forget to increase your free space map settings


--
Decibel!, aka Jim C. Nasby, Database Architect  [EMAIL PROTECTED]
Give your computer some brain candy! www.distributed.net Team #1828




smime.p7s
Description: S/MIME cryptographic signature

[GENERAL] Conditional ordering operators

2008-02-06 Thread Sergey Konoplev 
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
Description: Binary data

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