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

On Wed, Apr 24, 2013 at 2:44 AM, Rafał Pietrak <rafal@zorro.isa-geek.com> wrote:

W dniu 03/24/2013 12:11 PM, Rafał Pietrak pisze:

W dniu 03/24/2013 12:06 PM, Misa Simic pisze:

maybe,

SELECT DISTINCT issuer,amount, array_agg(REFERENCE) over (partition by
invoice_nr) from invoices;


RIGHT. Thenx. (and the first thing I did, I've read the doc on
array_agg().... what stress makes from people :(


Actually, I have a problem with that (which I haven't noticed earlier
because the data I'm having, don't have to many "duplicates" that cause it).
The problem is, that:
--------------------------------------
SELECT DISTINCT a, b, c, array_agg(k.d) OVER (PARTITION BY k.c )  FROM testy
k where k.e <> 'email' and k.c='1035049' ; a   |    b     |    c    |           array_agg
------+----------+---------+-------------------------------1035 | 10410053 | 1035049 | {9902031328529,5951948640868}
---------------------------------------

is _almost_ fine. But I actually need to have control over the order in
which the array gathered its values. So I try:
------------------------------------
SELECT DISTINCT a, b, c, array_agg(k.d) OVER (PARTITION BY k.c ORDER BY k.e)
FROM testy k where k.e <> 'email' and k.c='1035049' ;

you are aware of in-aggregate ordering (not completely sure if it
meets your use case?

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;

No, I don't (manual: http://www.postgresql.org/docs/9.1/static/tutorial-window.html, have just one word "distinct" on that page, and it's not in the above context). And I cannot duplicate the above:
--------------------------------------------
# select array_agg(distinct v order by v desc) from (select generate_series(1,3) v union all select generate_series(1,3)) q;
ERROR:  syntax error at or near "order"
LINE 1: select array_agg(distinct v order by v desc) from (select ge...
--------------------------------------------

Did I miss something??

In the mean time, I was working towards:
--------------------------------------------
# with ktkt(b,l,s,t) as (SELECT a, b, c, array_agg(k.d)OVER (PARTITION BY k.c ORDER BY k.e)   FROM testy k where k.e <> 'email') select distinct on (b,l,s) b,l,s,t from ktkt k where  k.s='1035049' order by b,l,s,array_length(t,1) desc;
  b   |    l     |    s    |               t              
------+----------+---------+-------------------------------
 1035 | 10410053 | 1035049 | {9902031328529,5951948640868}
(1 row)
-------------------------------------------

Which gives the "expected" result, not exactly, because:
.... my final goal is to select one contact information for an "entity", which is, say: telephone, and which is, say: mobile. Taking into account, that the main contact information table is roughly: CREATE TABLE testy (id_a, id_b, id_c, conact_value, contact_kind, primary key (id_a, id_b,id_c)). ... I cannot collapse the multiple identification columns - they collectively form a unique ID, of an entity. That main contact information table has associated tables like to "fixed/mobile" ("testy" has additional FK columns for that).

And the above "partial result" isn't working towards my final goal.

But, while writing this response, It occured to me, that, may be I shouldn't build the array  so early in the query, but start with a wider join (only reduced by the desired contact attributes) ..... OK. I'll do some testing with that.

Still, I'll be greatfull for some explanations why the "distinct" disdn't work for me. May be that would be a tool for this case.

thnx,

-R