How to add result of generate_series to array in PostgreSQL?

Question

PostgreSQL 9.5.4.

I have a function which returns array of days between two dates in string form. It's based on this code:

select (generate_series('2012-06-29', '2012-07-03', '1 day'::interval))::date

This code gives five dates in output, it's ok.

But if I do this in function:

DECLARE
dates date[];

BEGIN
select (generate_series('2012-06-29', '2012-07-03', '1 day'::interval))::date into dates;
return array_to_string(dates, ',');
END;

Then there is an error like this: "Invalid array literal '2012-06-29'. The value of the array must begin with "{" or specify the dimension. "

How can I fix this?

Answer 1

You can use the array constructor:

DECLARE
  dates date[];
BEGIN
  select array(select generate_series('2012-06-29', '2012-07-03', '1 day'::interval)::date)
    into dates;  --need semicolon here
  return dates;
END;

If that code is actually a function, then you can simplify it to a SQL function

create function get_dates()
  returns date[]
$$
  select array(select generate_series(current_date - 6, current_date, interval '1' day)::date);
$$
language sql;

Answer 2

"Invalid array literal '2012-06-29'. The value of the array must begin with "{" or specify the dimension. "

SELECT returns a row set,

ARRAY() function can convert row set into an array. and the reverse function is UNNEST().