Re: Use of array_agg and array string on inner query

On Wed, May 18, 2016 at 1:07 PM, shankha <shankhabanerjee(at)gmail(dot)com> wrote:

>
> /* 3. */ SELECT c1, c2,
> ARRAY_TO_STRING(ARRAY_AGG((SELECT t3.c1 FROM s.t1 as t3 JOIN s.t1
> as t2 ON t3.c3 = t2.c2)), ',')
> FROM s.t1 t1
> GROUP BY c1;
> DROP SCHEMA s CASCADE;
>

​The following adjustments should work:​

​array_to_string(select array_agg(t3.c1) FROM ... WHERE t2.c1 = t1.c1), ','

​The array_agg needs to moved into the subquery - which causes an implicit
GROUP BY to be added to the subselect thus ensuring only one row is
returned for processing by the array_to_string function.

​You need to add the where clause to make the subquery correlate to the
outer query.​

​You may need to play with it a bit as I didn't try running your example.​

David J.