Postgresql 支持数组,但是没有对数据内部元素进行排序的一个函数。 今天我分别用PLPGsql和PLPYTHONU写了一个。
示例表结构:
t_girl=#\dtest_array; Table"ytt.test_array" Column|Type|Modifiers --------+-----------+--------------------------------------------------------- id|integer|notnulldefaultnextval('test_array_id_seq'::regclass) str1|integer[]| Indexes: "test_array_pkey"PRIMARYKEY,btree(id)
示例数据:
t_girl=#select*fromtest_array; id|str1 ----+--------------------------- 1|{100,200,300,5,10,20,100} 2|{200,100,2,30,5} 3|{2000,101,10} (3rows) Time:1.513ms
升序
t_girl=#selectid,array_sort(str1,'asc')fromtest_array; id|array_sort ----+--------------------------- 1|{5,300} 2|{0,200} 3|{0,2000} (3rows) Time:2.377ms
降序
t_girl=#selectid,'desc')fromtest_array; id|array_sort ----+--------------------------- 1|{300,5} 2|{200,0} 3|{2000,0} (3rows) Time:3.318ms t_girl=#
python 存储函数array_sort_python 执行结果:
降序:
t_girl=#selectid,array_sort_python(str1,'desc')fromtest_array; id|array_sort_python ----+--------------------------- 1|{300,0} (3rows) Time:2.797ms
升序:
t_girl=#selectid,'asc')fromtest_array; id|array_sort_python ----+--------------------------- 1|{5,2000} (3rows) Time:1.856ms t_girl=#
附: array_sort_python 代码:
CREATEorreplaceFUNCTIONarray_sort_python(c1text[],f_ordertext)RETURNStext[]AS$$ result=[] iff_order.lower()=='asc': c1.sort() result=c1 eliff_order.lower()=='desc': c1.sort(reverse=True) result=c1 else: pass returnresult $$LANGUAGEplpythonu;
array_sort 代码:
createorreplacefunctionarray_sort(anyarray,f_ordertext)returnsanyarray as $ytt$ declarearray1aliasfor$1; tmpint; resulttext[]; begin iflower(f_order)='desc'then fortmpinselectunnest(array1)asaorderbyadesc loop result:=array_append(result,tmp::text); endloop; returnresult; elsiflower(f_order)='asc'then fortmpinselectunnest(array1)asaorderbyaasc loop result:=array_append(result,tmp::text); endloop; returnresult; else returnarray['f_ordermustbeascordesc!']; endif; end; $ytt$languageplpgsql;