Re: is there a way to deliver an array over column from a query window?

W dniu 04/26/2013 12:25 AM, Merlin Moncure pisze:
[--------------------------]
>>
>> select array_agg(v order by v desc) from generate_series(1,3) v;
>>
>> also, 'distinct'
>> select array_agg(distinct v order by v desc) from (select
>> generate_series(1,3) v union all select generate_series(1,3)) q;
>>
[--------------------]
> This feature was added w/9.0.   This means you are on 8.4.  Time to upgrade...
>
>
I tried it, and it looks, like I can have just one column "processed" by 
the contruct "distinct ... order..." written under array_agg(). So it 
apears, that its "sole purpose" is to yield an array with distinct 
values, should there be duplicates in the source (table or query).

Having distinct values is fine, but I actually need to have the 
resulting array, contain values in sort-order, which is defined by 
values in another column. like:

array_agg(distinct v order by v) -- works in postgres, but actually I need:
array_agg(distinct v order by v,x) -- which doesn't. (ERROR: 
....expressions must appear in argument list), but that suggestion is 
not possible here, e.g.:
array_agg(distinct v,x order by v,x) -- is actually a two argument 
array_agg() function call.

Elaborating on the second example above: what I need is: "feed the 
array_agg() only with distinct (by sorting for uniqness) v-values, but 
feed them sorted according to x-values". This is what I need.

I'd apreciate other ideas (my yesterday plan to avoid array untill late 
in the query failed). Any sugestions welcome. (Or may be I'm just plain 
wrong about the above "distinct" construct - then will apreciate a 
pointer to some documentation).

thnx,

-R