Search Postgresql Archives

Re: table name "unnest" specified more than once

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Thursday, February 27, 2020, Guyren Howe <guyren@xxxxxxxxx> wrote:
# 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

This specific error is resolved by using the alias feature of the FROM clause:

alias

A substitute name for the FROM item containing the alias. An alias is used for brevity or to eliminate ambiguity for self-joins (where the same table is scanned multiple times) 


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.

Putting unnest in a select-list might get you closer to your goal.  Or the array-array concatenating operator.
 

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?

Something like:

Select f1.a, f2.b
From (select * from unnest(arr)) as f1 (a)
Cross join (select * From unnest(arr)) as f2 (b)
Etc...Maybe with parentheses...

There may be a more succinct way to write this but going verbose until it works minimizes the amount of syntax you need to deal with.  Though I personally encourage writing intentional cross join (really, all joins) using join syntax instead of comma-separated from items.

David J.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux