current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Join the Stack Overflow Community
Stack Overflow is a community of 6.3 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 1 down vote favorite
1
 
{{5,23}, {8,45}, {1,12}}

I want to sort this array according the first element of each sub-array elements, like;

{{1,12}, {5,23}, {8,45}}

How can I do that?

Edit:

This code works;

create or replace function arraysortingaccordingfirstindexofsubarrayelements()
returns void as $$
declare samplearraydata integer[][];
declare sortedarraydata int[][];
begin
samplearraydata:=ARRAY[[5,8], [1,6],[3,9]];
EXECUTE 'CREATE TEMP TABLE temptable (
   firstindex integer,
   secondindex integer
) on commit drop;';
WITH
    data as (select samplearraydata as arr) 
insert into temptable select   
    arr[i][1], 
    arr[i][2] FROM data, 
    generate_subscripts((SELECT arr FROM data), 1) i 
    order by 1;
sortedarraydata:=(SELECT array_agg_mult(ARRAY[ARRAY[y.firstindex, y.secondindex]])) FROM   temptable y;
raise notice '%', sortedarraydata;
end;
$$ language plpgsql;

CREATE AGGREGATE array_agg_mult (anyarray)  (
    SFUNC     = array_cat
   ,STYPE     = anyarray
   ,INITCOND  = '{}'
);

CREATE TEMP TABLE arrtbl (
   firstindex integer,
   secondindex integer
) on commit drop;

Credits to Erwin:)

share|improve this question
    
As always, please your version of Postgres. And are you taking values from table rows or just dealing with a single value? – Erwin Brandstetter Jun 15 '15 at 19:55
    
@Erwin Brandstetter 9.4 i am taking values from table rows...i am gonna sort the values of array that is being in every row and then update it in sorted way. – cinfis Jun 15 '15 at 19:59
up vote 1 down vote accepted

Aggregate function

The built-in aggregate function array_agg() currently (Postgres 9.4) only works for non-array input data types. Since we are going to aggregate arrays, we need a custom aggregate function like detailed in this related answer:

CREATE AGGREGATE array_agg_mult (anyarray)  (
    SFUNC     = array_cat
   ,STYPE     = anyarray
   ,INITCOND  = '{}'
);

The upcoming Postgres 9.5 ships a built-in version of this aggregate function (which is considerably faster). Per devel documentation:

Function                Argument Type(s)   Return Type
array_agg(expression)   any array type     same as argument data type  

Description
input arrays concatenated into array of one higher dimension
(inputs must all have same dimensionality, and cannot be empty or NULL)

Accessing array

Basic understanding of array syntax / how to access arrays is required. Read this chapter of the manual if you aren't there, yet.

Query

Then, based on the same setup as this earlier answer today (you may want to read that one, too):

SELECT arrtbl_id
     , array_agg_mult(arr[i:i][lo2:up2] ORDER BY arr[i:i][lo2])
FROM  (SELECT arrtbl_id, arr
            , array_lower(arr,2) AS lo2
            , array_upper(arr,2) AS up2
       FROM   arrtbl) t
     , generate_subscripts(t.arr,1) i
GROUP  BY 1;

Explanation

  1. Compute lower and upper bound for the 2nd array dimension in the base table, that's cheaper than repeating it for every array slice.

    I am not simply starting with index 1 to cover a possible corner case. Postgres allows non-standard array subscripts:

  2. LATERAL join the base table to generate_subscripts(arr,1) to get the first index for each sub-array (no matter how many dimensions).

  3. The expression arr[i:i][lo2:up2] returns each array slice preserving dimensions. This works for any number of elements and dimensions (greater than 1).

  4. The expression arr[i:i][lo2:lo2] returns the first slice within each array slice, which defines sort order. For strictly 2-dimnesional arrays, you can use arr[i][lo2] to return the first element of each slice as well, but the former works for any dimensionality greater than 1.

  5. Aggregate with array_agg_mult() which happily accepts values of appropriate dimension (everything falls into place nicely). Or use the built-in array_agg() for arrays in pg 9.5+.

share|improve this answer
    
thank you so much sir this is very helpfull explanation... – cinfis Jun 16 '15 at 9:50
    
i wrote the code(above) and it works... – cinfis Jun 16 '15 at 16:07
    
sorry i have forgot to do this...thanks again:) – cinfis Nov 28 '15 at 21:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.