multiple SRFs in SELECT clause.

Started by Kris Jurkaover 19 years ago2 messagesbugs
Jump to latest
#1Kris Jurka
books@ejurka.com

I'm getting some odd results when running two generate_series calls in a
SELECT. When the two calls return the same number of rows you get that
many rows out:

# SELECT generate_series(1,3), generate_series(1,3);
generate_series | generate_series
-----------------+-----------------
1 | 1
2 | 2
3 | 3
(3 rows)

When the row counts differ you get the least common multiple number of
rows.

# SELECT generate_series(1,4), generate_series(1,2);
generate_series | generate_series
-----------------+-----------------
1 | 1
2 | 2
3 | 1
4 | 2
(4 rows)

I was personally expecting a cross join between them that would be
equivalent to

# SELECT * FROM generate_series(1,4) a, generate_series(1,2) b;
a | b
---+---
1 | 1
1 | 2
2 | 1
2 | 2
3 | 1
3 | 2
4 | 1
4 | 2
(8 rows)

Tested on 8.1.3 and CVS HEAD.

Kris Jurka

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Kris Jurka (#1)
Re: multiple SRFs in SELECT clause.

Kris Jurka <books@ejurka.com> writes:

I'm getting some odd results when running two generate_series calls in a
SELECT ...
When the row counts differ you get the least common multiple number of
rows.

Yup, this is the expected or at least historical behavior. It's not
entirely clear what you *should* get, which is one reason we ought to
deprecate SRFs in SELECT lists.

regards, tom lane