Re: A unique pairs version of UNNEST() ?

Andy Colson <andy(at)squeakycode(dot)net> wrote:

>> So in an example where list_of_ids is {1,2,3,4,5} I would essentially get:
>>
>> {1, 2}
>> {1, 3}
>> {1, 4}
>> {1, 5}
>> {2, 3}
>> {2, 4}
>> {2, 5}
>> {3, 4}
>> {3, 5}
>> {4, 5}
>>
>>
>> Any tips? Thanks!
>>
>> --
>> Wells Oliver
>> wells(dot)oliver(at)gmail(dot)com <mailto:wellsoliver(at)gmail(dot)com>
>
> if you could convert the array to a table then cross join it. Something
> like:
>
> select a.*, b.*
> from unnest( {1,2,3,4,5} ) a
> cross join unnest( {1,2,3,4,5} ) b
>
> -Andy

not exactly the expectet result (and syntactically wrong), better
solution:

test=*# select (a.*, b.*) from unnest( array[1,2,3,4,5] ) a cross join unnest( array[1,2,3,4,5] ) b where a < b;
row
-------
(1,2)
(1,3)
(1,4)
(1,5)
(2,3)
(2,4)
(2,5)
(3,4)
(3,5)
(4,5)
(10 rows)

or

test=*# select array[a.*, b.*] from unnest( array[1,2,3,4,5] ) a cross join unnest( array[1,2,3,4,5] ) b where a < b;
array
-------
{1,2}
{1,3}
{1,4}
{1,5}
{2,3}
{2,4}
{2,5}
{3,4}
{3,5}
{4,5}
(10 rows)

(matches the excpected result)

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°