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?

Community · Accepted Answer · 2019-09-10 19:44:26Z

14

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;

edited Sep 10, 2019 at 19:44

CommunityBot

11 silver badge

answered Apr 8, 2017 at 19:21

user330315

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

Comments

Ben · Accepted Answer · 2017-04-09 04:55:40Z

1

"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().

answered Apr 9, 2017 at 4:55

Ben

1,3411 gold badge20 silver badges33 bronze badges

Collectives™ on Stack Overflow

How to add result of generate_series to array in PostgreSQL?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related