Re: is there a way to deliver an array over column from a query window? - Mailing list pgsql-general

From Rafał Pietrak
Subject Re: is there a way to deliver an array over column from a query window?
Date
Msg-id 517AED39.30300@zorro.isa-geek.com
Whole thread Raw
In response to Re: is there a way to deliver an array over column from a query window?  (Misa Simic <misa.simic@gmail.com>)
List pgsql-general
W dniu 04/26/2013 09:54 PM, Misa Simic pisze:
 SELECT DISTINCT a, b, c, array_agg(d)  OVER (PARTITION BY c )  FROM

(

SELECT a, b, c, d FROM  testy where e <> 'email' and c='1035049'  ORDER BY  a, b, c, e

) t

Doesnt give u desired result?

Hmm... actualy, it looks like it does. I wouldn't thought, that the sort order is maintaned from subquery, but if it does, this is just it.

It looks like I've just overdone the solution.

-R




On Friday, April 26, 2013, Rafał Pietrak wrote:
W dniu 04/26/2013 05:25 PM, Tom Lane pisze:
Rafał Pietrak <rafal@ztk-rp.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



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

pgsql-general by date:

Previous
From: David Johnston
Date:
Subject: Re: Where to set search_path
Next
From: Yang Zhang
Date:
Subject: Optimizing bulk update performance