[Original] PostgreSQL sorts Arrays

PostgreSQL supports arrays, but does not have a function to sort internal data elements. Today I wrote one with PLPGSQL and PLPYTHONU respectively. Example Table Structure: t_girl # dtest_array; Tableytt. test_arrayColumn | Type | Modifiers -------- + ----------- + -------------------------------------

PostgreSQL supports arrays, but does not have a function to sort internal data elements. Today I wrote one with PLPGSQL and PLPYTHONU respectively. Example Table Structure: t_girl = # \ dtest_array; Table "ytt. test_array" Column | Type | Modifiers -------- + ----------- + -------------------------------------

PostgreSQL supports arrays, but does not have a function to sort internal data elements. Today I wrote one with PLPGSQL and PLPYTHONU respectively.

Example Table Structure:

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)

Sample Data:

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 storage function array_sort execution result:

Ascending

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

Descending Order

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 mst_girl=#

Python storage function array_sort_python execution result:

Descending order:

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

Ascending Order:

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 mst_girl=#

Appendix: array_sort_python code:

CREATE or replace FUNCTION array_sort_python(c1 text [],f_order text) RETURNS text [] AS $$result = []if f_order.lower() == 'asc':    c1.sort()    result = c1elif f_order.lower() == 'desc':    c1.sort(reverse=True)    result = c1else:    passreturn result$$ LANGUAGE plpythonu;

Array_sort code:

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;