# select
*
from
unnest(array[array['a', 'b'], array['c', 'c']]),
unnest(array[array['1', '2'], array['3', '4']]);
ERROR: 42712: table name "unnest" specified more than once
I’m trying to cross-join multiple two-dimensional arrays, expecting to retain
the inner arrays. I’ve been trying for hours without luck; the ever-esoteric
SQL syntax foils me at every turn.
It’s a shame I can’t get the unnest function not to just concatenate the inner
arrays if I just put a bunch of them. This doesn’t strike me as optimal
behavior.
For more context, I’m trying to make a system of functions to score a Texas
Hold ‘Em game. So I have a card type consisting of a pair of suit and rank, and
I’m tossing them about. The cross-join is so I can build all candidate hands
for scoring. I’m trying to create a function I can call like this:
select
best_hands_with_river(
array[
c('H', 'K'),
c('D', 'A')
],
array[
c('C', '2'),
c('C', 'K'),
c('S', 'K'),
c('H', 'A'),
c('C', 'A')
])
Here, c is a function that constructs a card type. Card is a ROW(varchar,
varchar).
So: how do I cross-join three identical arrays of my card type?
