CODEWITHSUNDEEP
postgresql
Eric Brooks
Eric Brooks

Reputation: 667

Loop postgresql Function through Date Range

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.

Upvotes: 1

Views: 5322

Answers (1)

klin
klin

Reputation: 121694

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.

Upvotes: 6

Related Questions