Sorting router interfaces

From: Brian Sherwood <bdsher(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Sorting router interfaces
Date: 2010-11-01 12:15:24
Message-ID: AANLkTinfjpHjk7thZ1e6=RtPSY6n8wDxR9spe5bHJcWX@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

I am trying to sort router interface names.
The problem is that I am doing a text sort and need to do a numerical sort.

I want the interfaces to be in numerical order:

 GigabitEthernet1/0/1    | 1/0/1     | {1,0,1}
 GigabitEthernet1/0/2    | 1/0/2     | {1,0,2}
 GigabitEthernet1/0/3    | 1/0/3     | {1,0,3}
etc.....

What I get instead is the following text ordering:

 GigabitEthernet1/0/1    | 1/0/1     | {1,0,1}
 GigabitEthernet1/0/10   | 1/0/10    | {1,0,10}
 GigabitEthernet1/0/11   | 1/0/11    | {1,0,11}
 GigabitEthernet1/0/12   | 1/0/12    | {1,0,12}
 GigabitEthernet1/0/13   | 1/0/13    | {1,0,13}
 GigabitEthernet1/0/14   | 1/0/14    | {1,0,14}
 GigabitEthernet1/0/15   | 1/0/15    | {1,0,15}
 GigabitEthernet1/0/16   | 1/0/16    | {1,0,16}
 GigabitEthernet1/0/17   | 1/0/17    | {1,0,17}
 GigabitEthernet1/0/18   | 1/0/18    | {1,0,18}
 GigabitEthernet1/0/19   | 1/0/19    | {1,0,19}
 GigabitEthernet1/0/2    | 1/0/2     | {1,0,2}
 GigabitEthernet1/0/20   | 1/0/20    | {1,0,20}
 GigabitEthernet1/0/21   | 1/0/21    | {1,0,21}
 GigabitEthernet1/0/22   | 1/0/22    | {1,0,22}
 GigabitEthernet1/0/23   | 1/0/23    | {1,0,23}
 GigabitEthernet1/0/24   | 1/0/24    | {1,0,24}
 GigabitEthernet1/0/25   | 1/0/25    | {1,0,25}
 GigabitEthernet1/0/26   | 1/0/26    | {1,0,26}
 GigabitEthernet1/0/27   | 1/0/27    | {1,0,27}
 GigabitEthernet1/0/28   | 1/0/28    | {1,0,28}
 GigabitEthernet1/0/29   | 1/0/29    | {1,0,29}
 GigabitEthernet1/0/3    | 1/0/3     | {1,0,3}
 GigabitEthernet1/0/30   | 1/0/30    | {1,0,30}
 GigabitEthernet1/0/31   | 1/0/31    | {1,0,31}
 GigabitEthernet1/0/32   | 1/0/32    | {1,0,32}
 GigabitEthernet1/0/33   | 1/0/33    | {1,0,33}

FYI: I also have entries like the following:
 lc-5/2/0.32769          | 5/2/0.32769  | {5,2,0.32769}
 irb.5                   | .5           | {.5}
 irb.51                   | .51          | {.51}
 irb.52                   | .52          | {.52}
 ae6                      | 6            | {6}
 ae7                      | 7            | {7}
 lo0.0                    | 0.0          | {0.0}
 Vlan710                  | 710          | {710}
 Vlan760                  | 760          | {760}
 Vlan910                  | 910          | {910}
 Vlan910                  | 910          | {910}
 gre                     |               | {""}
 tap                     |               | {""}
 dsc                     |               | {""}

The above listings are produced with the following:

SELECT
       interface,
       regexp_replace(interface,'[A-Za-z -]+','','g') as "sort_col1",
       regexp_split_to_array(regexp_replace(interface,'[A-Za-z
-]+','','g'),E'/') as "sort_col"
FROM all_ports
ORDER BY devicename,sort_col

I have tried to break out the interface number to a separate array
column to sort on and was hoping to cast the array to a float[], but
no luck:

SELECT
       interface,
       regexp_replace(interface,'[A-Za-z -]+','','g') as "sort_col1",
       regexp_split_to_array(regexp_replace(interface,'[A-Za-z
-]+','','g'),E'/')::float as "sort_col"
FROM all_ports

psql:-:15: ERROR:  cannot cast type text[] to double precision
LINE 5: ...gexp_replace(interface,'[A-Za-z -]+','','g'),E'/')::float as...

Can anyone suggest a better approach or help with this approach?

Thanks

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Filip Rembiałkowski 2010-11-01 21:37:54 Re: Sorting router interfaces
Previous Message Rob Sargent 2010-10-31 23:07:32 Re: large xml database