On 15 February 2011 02:06, YAMAMOTO Takashi <yamt@mwd.biglobe.ne.jp> wrote:
> hi,
>
> the following behaviour of multiple generate_series in a select seems
> a little counter-intuitive to me. i expected that the former returns 4 rows.
> where should i look to learn the exact semantics? (documentation and/or code)
>
> YAMAMOTO Takashi
>
> test=# select generate_series(1,2) as a,generate_series(1,2) as b;
> a | b
> ---+---
> 1 | 1
> 2 | 2
> (2 rows)
>
> test=# select generate_series(1,2) as a,generate_series(1,3) as b;
> a | b
> ---+---
> 1 | 1
> 2 | 2
> 1 | 3
> 2 | 1
> 1 | 2
> 2 | 3
> (6 rows)
The output of such queries will keep producing output until all
generate_series functions are at their end simultaneously. I think
this may be due to none of the functions actually getting to decide
when the series ends unless it's unanimous... or something like that.
If you're looking for the first one to product 4 rows as its output,
you may actually want:
postgres=# select * from generate_series(1,2) as a,generate_series(1,2) as b;
a | b
---+---
1 | 1
1 | 2
2 | 1
2 | 2
(4 rows)
If you select FROM generate_series, they start getting treated like
tables, and you end up with a cartesian product.
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935