Support for set returning functions (generate_series)

[surjikal]

Set returning functions like generate_series are incredibly useful in Postgres. Would be neat to have them in presto!

Postgres Set Returning functions:
http://www.postgresql.org/docs/9.4/static/functions-srf.html

Consider the following use case:

I have a table of orders. An order has a timestamp and a total. I want to make a report of daily order totals. Also, let's say the store is closed on Mondays (i.e. there are no orders made on Mondays).

In order to get a continuous time series of totals, with all Mondays totalling to 0, I would generate a series of days, left join it with my orders table on timestamp (date trunc'd to day), and do coalesce(total, 0).

[snarayanank2]

generate_series is very very useful in practice. However, you can get quite
far using unnest http://prestodb.io/docs/current/sql/select.html

Example:
presto:default> SELECT i1
-> FROM (
-> VALUES
-> (ARRAY[1,2,3,4,5])
-> ) AS x (i)
-> CROSS JOIN UNNEST(i) AS t (i1);

i1

1
2
3
4
5
(5 rows)

I presume if you had a function that could return an arbitrary array
[1,2,..n] then you could replicate generate_series functionality.

Thanks
Siva

On Thu, Jan 1, 2015 at 2:58 PM, Nick Porter notifications@github.com
wrote:

This function is incredibly useful in Postgres. Would be neat to have it
in presto!

Postgres Set Returning functions:
http://www.postgresql.org/docs/9.4/static/functions-srf.html

Consider the following use case:

I have a table of orders, and an order has a timestamp and a total. I want
to make a report of daily order totals. Also, let's say the store is closed
on Mondays (i.e. there are no orders made on Mondays).

In order to get a continuous time series of totals, with all Mondays
totalling to 0, I would generate a series of days, join it with my orders
table on timestamp (date trunc'd to day), and do coalesce(total, 0).

—
Reply to this email directly or view it on GitHub
#2169.

[Himmetk]

so how can we generate series like for example 0,3,5,7......,99?

EDIT: I did with small arrangement:
SELECT i1
FROM (SELECT SEQUENCE(1,100,7)
) AS x (i)
CROSS JOIN UNNEST(i) AS t (i1);

[stale]

This issue has been automatically marked as stale because it has not had any activity in the last 2 years. If you feel that this issue is important, just comment and the stale tag will be removed; otherwise it will be closed in 7 days. This is an attempt to ensure that our open issues remain valuable and relevant so that we can keep track of what needs to be done and prioritize the right things.

[chananelp]

Shorter version of "generate series like for example 0,3,5,7......,99?":
(no need the CROSS JOIN..)

SELECT i1 FROM UNNEST(SEQUENCE(1,100,7)) AS t (i1)