pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSorting router interfaces
3+ messages / 3 participants
[nested] [flat]
* Sorting router interfaces
@ 2010-11-01 12:15 Brian Sherwood <bdsher@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: Brian Sherwood @ 2010-11-01 12:15 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Sorting router interfaces
@ 2010-11-01 21:37 Filip Rembiałkowski <filip.rembialkowski@gmail.com>
parent: Brian Sherwood <bdsher@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Filip Rembiałkowski @ 2010-11-01 21:37 UTC (permalink / raw)
To: Brian Sherwood <bdsher@gmail.com>; +Cc: pgsql-sql
2010/11/1 Brian Sherwood <bdsher@gmail.com>:
> 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?
good approach, jus needed to be generalized...
filip@filip=# SELECT interface,
regexp_replace( interface, '[0-9].*', '' ) as before_1st_digit,
regexp_replace( interface, '^[^0-9]+', '' ) as from_first_digit,
CASE WHEN interface ~ '[0-9]' THEN
regexp_split_to_array( regexp_replace( interface, '^[^0-9]+', ''
), '[^0-9]+' )::int[]
ELSE array[-1] END as nums
FROM interfaces
ORDER BY 2,4;
interface | before_1st_digit | from_first_digit | nums
-----------------------+------------------+------------------+----------
eth0 | eth | 0 | {0}
eth0/0 | eth | 0/0 | {0,0}
eth0/1 | eth | 0/1 | {0,1}
eth0/10 | eth | 0/10 | {0,10}
eth1 | eth | 1 | {1}
GigabitEthernet1/0/2 | GigabitEthernet | 1/0/2 | {1,0,2}
GigabitEthernet1/0/20 | GigabitEthernet | 1/0/20 | {1,0,20}
irb.5 | irb. | 5 | {5}
irb.51 | irb. | 51 | {51}
tun | tun | | {-1}
tun0 | tun | 0 | {0}
Vlan72 | Vlan | 72 | {72}
Vlan710 | Vlan | 710 | {710}
(13 rows)
--
Filip Rembiałkowski
JID,mailto:filip.rembialkowski@gmail.com
http://filip.rembialkowski.net/
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Sorting router interfaces
@ 2010-11-02 00:23 Andreas <maps.on@gmx.net>
parent: Brian Sherwood <bdsher@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Andreas @ 2010-11-02 00:23 UTC (permalink / raw)
To: pgsql-sql
Am 01.11.2010 13:15, schrieb Brian Sherwood:
> 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.
>
>
> 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}
This was the easy part.
Suppose those lines above were the input table "interfaces" and the
columns were called c1, c2, c3.
Since c3 is allready an array you could do this:
select *
from interfaces
order by (c3::integer[])[1], (c3::integer[])[2], (c3::integer[])[3]
Records of this type
> lc-5/2/0.32769 | 5/2/0.32769 | {5,2,0.32769}
work with:
order by (c3::float[])[1], (c3::float[])[2], (c3::float[])[3]
Now you "just" need to identify those records which wont produce such
nice numerical arrays.
Then split the two sets up, sort them in separate selects, add a set_nr
and a row_number() as row_nr.
Then UNION both sets together again
AND eventually do an ORDER BY set_nr, row_nr
and you are allready done.
OK, that would be just an idea :)
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2010-11-02 00:23 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-11-01 12:15 Sorting router interfaces Brian Sherwood <bdsher@gmail.com>
2010-11-01 21:37 ` Filip Rembiałkowski <filip.rembialkowski@gmail.com>
2010-11-02 00:23 ` Andreas <maps.on@gmx.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox