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

W dniu 04/26/2013 05:25 PM, Tom Lane pisze:
> =?ISO-8859-2?Q?Rafa=B3_Pietrak?= <rafal(at)ztk-rp(dot)eu> writes:
>> 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),
> Why do you think you need that? AFAICS, the extra order-by column could
> not in any way affect the result of the operation.

In my particular case (e.g. not in general, since I assume, we all
agree, that people do sort things comming out of the query for one
purpose or another), is that:
1. the information i retrieve (the V), is a telephone number.
2. my database does keep numerous contact information (e.g. telephone
numbers, email, etc) for "entities" registered here - e.g
people/companies leave contact information of various relevance:
my-private, my-office, my-lawyer, etc.
3. when I need to get in touch with somebody, I need to choose the
number that is "most relevant" - one person leaves "my-private" phone,
and "my-lawyer" phone; the other leaves "my-office", and "my-lawyer".
4. in the above example I'd like to peek: "my-private" for the first
person, and "my-office" for the other. I wouldn't like to relay on
randomness provided by the database query plan.
5. so I have "the other" column (the X, e.g "my-something"), that I'd
like to sort the array elements by. And peek just the first element of
the array.

BTW: I've just rid off the array, and cooked a plain table join with
"distinct on ()", which gives just what I needed. My initial plan of
using array was to reduce the intermediate row-sets as much as possible
as early as possible. Yet, in this case, plain old RDB joins proved to
be better (may be not faster - a big multitable join is formed along the
query, but conceptually cleaner, which works for me, the database isn't
terribly big).

So I have my problem solved, although I haven't figured out a way to
have controll over the sort order of array_agg() result - which might be
otherwise usefull.

thnx,

-R