Loop postgresql Function through Date Range

Question

I have a user defined function. This question shows how to loop through dates. Using this approach, I tried this query:

select myfun(a::date) from generate_series('2015-01-01'::date,'2016-01-27','1 day') s(a)

This doesn't quite work. What it returns is a single column of the form:

(10101, "Sample",  "test")
(10102, "Sample2", "test2")

When in reality there should be three columns. It merges them into one.

I noticed that this is the same behavior that you get in a vanilla query such as select mytable when I omit the asterisk. The above query doesn't have an asterisk in it, but adding one causes an error.

klin · Accepted Answer · 2020-01-20 02:30:13Z

6

Place the function call in the FROM clause:

select f.*
from 
    generate_series('2015-01-01'::date,'2016-01-27','1 day') s(a),
    myfun(a::date) f;

or using more formal syntax:

select f.*
from generate_series('2015-01-01'::date,'2016-01-27','1 day') s(a)
cross join myfun(a::date) f;

This form of the FROM clause is known as lateral join.

edited Jan 20, 2020 at 2:30

answered Jan 27, 2016 at 23:30

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Loop postgresql Function through Date Range

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related