Generating Table / array on the fly in postgresql function

Question

I need to create postgresql function

CREATE FUNCTION date_ranges (_start date, end date) 
  RETURNING TABLE(day_in_range date) AS...

if I call date_ranges('2010-06-01', 2010-06-05') I should receive

Any Ideas how to do it?

Look at the last example: postgresql.org/docs/8.2/static/functions-srf.html — OMG Ponies
– OMG Ponies, Commented Jun 16, 2010 at 23:54

rfusca · Accepted Answer · 2010-06-17 00:20:24Z

5

If you're on Postgresql 8.4:

SELECT generate_series(_start ::timestamp,_end ::timestamp,'1 day');

Example:

postgres=# SELECT generate_series('2010-06-01'::timestamp,
postgres-# '2010-06-05'::timestamp,'1 day')::date;
 generate_series
-----------------
 2010-06-01
 2010-06-02
 2010-06-03
 2010-06-04
 2010-06-05

On older versions:

SELECT '2010-06-01'::date + step FROM
generate_series(0,'2010-06-05'::date - '2010-06-01'::date,1) AS t(step);

answered Jun 17, 2010 at 0:20

rfusca

7,7752 gold badges32 silver badges34 bronze badges

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

2 Comments

bensiu Over a year ago

Works perfect - THANK YOU (specially for older version I use 8.1) - bensiu

Frank Heikens Over a year ago

8.1 will be EOL this year, start upgrading to a newer version.

Collectives™ on Stack Overflow

Generating Table / array on the fly in postgresql function

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related