Postgresql – How does cast work with Set Returning Functions (SRF) like generate_series

castpostgresqlset-returning-functions

I got to wondering about how we would best use generate_series if we only want a series of dates. As a note, generate_series returns setof timestamp or setof timestamp with time zone (same as argument type). For example, observe this..

SELECT generate_series(
  '2018-01-01'::date,
  '2019-01-01'::date,
  '1 day'
);
    generate_series     
------------------------
 2018-01-01 00:00:00-06
 2018-01-02 00:00:00-06
 2018-01-03 00:00:00-06

In the above you'll notice the time portion. In order to generate dates, I tried,

SELECT cast(
  generate_series(
    '2018-01-01'::date,
    '2019-01-01'::date,
    '1 day'
  )
  AS date
);
 generate_series 
-----------------
 2018-01-01
 2018-01-02
 2018-01-03

To my surprise that worked! But, that raises the question, is this supported? If it is, is this documented. Even more awkward, in the above you'll see that cast is returning a setof, but if you move it to the from condition like this,

SELECT *
FROM cast(                                                     
  generate_series(
    '2018-01-01'::date,
    '2019-01-01'::date,
    '1 day'
  )
  AS date
);

You'll get back,

ERROR: set-valued function called in context that cannot accept a set

As a side note, it works in sub-selects and the dynamic can also be seen with int/reals

SELECT x/5 FROM (SELECT CAST(generate_series(1,10) AS real)) AS t(x);
SELECT x/5 FROM (SELECT CAST(generate_series(1,10) AS int)) AS t(x);

Question inspired from this answer

Best Answer

Cast the actual value, not the set.

SELECT v::date
FROM generate_series('2018-01-01'::date, '2019-01-01'::date, '1 day') as t(v);