PostgreSQL给数组排序 - 数据库编程

PostgreSQL 支持数组，但是没有对数据内部元素进行排序的一个函数。今天我分别用PLPGSQL和PLPYTHONU写了一个。
t_girl=# \d test_array; Table "ytt.test_array" Column | Type | Modifiers --------+-----------+--------------------------------------------------------- id | integer | not null default nextval('test_array_id_seq'::regclass) str1 | integer[] | Indexes: "test_array_pkey" PRIMARY KEY, btree (id)

t_girl=# select * from test_array; id | str1 ----+--------------------------- 1 | {100,200,300,5,10,20,100} 2 | {200,100,2,30,0,5} 3 | {2000,101,2,30,0,10} (3 rows) Time: 1.513 ms

plsql存储函数array_sort执行结果：
t_girl=# select id,array_sort(str1,'asc') from test_array; id | array_sort ----+--------------------------- 1 | {5,10,20,100,100,200,300} 2 | {0,2,5,30,100,200} 3 | {0,2,10,30,101,2000} (3 rows) Time: 2.377 ms

t_girl=# select id,array_sort(str1,'desc') from test_array; id | array_sort ----+--------------------------- 1 | {300,200,100,100,20,10,5} 2 | {200,100,30,5,2,0} 3 | {2000,101,30,10,2,0} (3 rows) Time: 3.318 ms t_girl=#

python 存储函数array_sort_python 执行结果：
t_girl=# select id,array_sort_python(str1,'desc') from test_array; id | array_sort_python ----+--------------------------- 1 | {300,200,100,100,20,10,5} 2 | {200,100,30,5,2,0} 3 | {2000,101,30,10,2,0} (3 rows)

Time: 2.797 ms

t_girl=# select id,array_sort_python(str1,'asc') from test_array; id | array_sort_python ----+--------------------------- 1 | {5,10,20,100,100,200,300} 2 | {0,2,5,30,100,200} 3 | {0,2,10,30,101,2000} (3 rows) Time: 1.856 ms t_girl=#

附: array_sort_python 代码:
CREATE or replace FUNCTION array_sort_python(c1 text [],f_order text) RETURNS text [] AS $$ result = [] if f_order.lower() == 'asc': c1.sort() result = c1 elif f_order.lower() == 'desc': c1.sort(reverse=True) result = c1 else: pass return result $$ LANGUAGE plpythonu;

create or replace function array_sort(anyarray,f_order text) returns anyarray
 as 
 $ytt$
declare array1 alias for $1;
              tmp int;
	      result text [];
begin
  if lower(f_order) = 'desc' then
    for tmp in select unnest(array1) as a order by a desc
    loop
      result := array_append(result,tmp::text);
    end loop;
    return result;
  elsif lower(f_order) = 'asc' then
    for tmp in select unnest(array1) as a order by a asc
    loop
      result := array_append(result,tmp::text);
    end loop;
    return result;
   else 
     return array['f_order must be asc or desc!'];
   end if;
end;
$ytt$ language plpgsql;