Make `generate_series` compatible with PostgreSQL

[Dandandan]

Describe the bug

generate_series generates a list:

> SELECT generate_series(1,5);
+------------------------------------+
| generate_series(Int64(1),Int64(5)) |
+------------------------------------+
| [1, 2, 3, 4, 5]                    |
+------------------------------------+
1 row(s) fetched. 
Elapsed 0.003 seconds.

However PostgreSQL generates rows:

SELECT generate_series(1,5)

generate_series
-----------------
               1
               2
               3
               4
               5
(5 rows)

To Reproduce

No response

Expected behavior

I expect it to be compatible with the PostgreSQL function, besides it should also be more efficient to generate columnar data than having to unnest it.

Additional context

No response

[jonathanc-n]

take

[2010YOUY01]

I think we can keep this behavior, and add another UDTF with the same name (which follows DuckDB behavior):

D select generate_series(1,3);
┌───────────────────────┐
│ generate_series(1, 3) │
│        int64[]        │
├───────────────────────┤
│ [1, 2, 3]             │
└───────────────────────┘
D select * from generate_series(1,3);
┌─────────────────┐
│ generate_series │
│      int64      │
├─────────────────┤
│               1 │
│               2 │
│               3 │
└─────────────────┘

I think it's also easy to use: we can operate both array and table column with generate_series()
Additionally, it would be great to let the table function only use constant memory. For example, if select * from genereate_series(1, 1000000000) it should generate data batch by batch, instead of materializing everything in TableScan node, then start output.
This way this function can be very useful in many cases like micro benchmarks and tests.

[jonathanc-n]

@2010YOUY01 Yeah I agree with this, I was working on this a bit and the current generate_series logic + output seems to follow through well with the other UDFs.

[simonvandel]

@Dandandan Can this issue be closed now, as DataFusion supports generate_series as a table function?