Postgres: Finding max value in an int array?

Question

using Postgres 9.3...

Can someone please explain why I can't use a max function directly on an unnested array..?

It is my understanding the unnest function returns a "setof" just like the select statement. So why does the short version of this query not work? (Am I missing something conceptually or is my issue a syntax related one?)

table: foo_history: 

id | history::smallint
----------------------------------- 
1  |  {10,20,30,50,40}

This doesn't work ?

Select id, max(unnest(history)) as vMax from foo_history;

...but this one does...?

WITH foo as (
    select id, unnest(history) as history 
    from foo_history
)
Select 
    id, max(history) as vMax
From foo 
Group by id;

Answer 1

If you install the intarray module it provides some extra array operators that'll let you write what you want, albeit somewhat inefficiently:

CREATE EXTENSION intarray;

SELECT id, (sort_desc(history))[1] as vMax
FROM foo_history;

It would be pretty easy to write greatest and least functions for arrays to add to intarray, the code is pretty simple.

Otherwise you can just write an SQL function:

CREATE OR REPLACE FUNCTION array_greatest(anyarray)
RETURNS anyelement
LANGUAGE SQL
AS $$
  SELECT max(elements) FROM unnest($1) elements
$$;

and use that:

SELECT id, array_greatest(history) as vMax
FROM foo_history;