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

W dniu 04/25/2013 03:44 PM, Merlin Moncure pisze:
> On Wed, Apr 24, 2013 at 2:44 AM, Rafał Pietrak <rafal(at)zorro(dot)isa-geek(dot)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