How do I check if an array is sorted with SQL?

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?

Answer

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;

Online example