I have the following table
CREATE TABLE arr(
id int,
arr_r int []
);
INSERT INTO arr(arr_r ) VALUES
( ARRAY [1 ,2 ,3]) ,
( ARRAY [4 ,3]) ,
( ARRAY [7 ,6]) ,
( ARRAY [2 ,2]);
and I want to output the arrays in the table which are sorted in ascending order. An output would be
1, 2, 3
2, 2
I tried some stuff with array_agg and an order by inside the array_agg but that did not work. How would I go about getting the desired output?
You can install the intarray extension, then you can do:
select *
from arr
where arr_r = sort(arr_r);
If you don't want to install the extension, you can create your own sort function:
create function sort(p_input int[])
returns int[]
as
$$
select array_agg(i order by i)
from unnest(p_input) as a(i);
$$
language sql
immutable;
You can eliminate duplicates by selecting unique values from unnest
create or replace
function sort_int_array(p_input int[])
returns int[]
as
$$
select array_agg(i order by i)
from ( select distinct unnest(p_input) i) a;
$$
language sql
immutable;
--- Test
select sort_int_array(array[1,3,2,5,7,5,3,6,8,8,9,0])
