I am trying to create a function in postgresql that returns a set of dates in between two dates with a certain interval. For example, If I pass on these two date parameters like most_recent_day = 2020-01-10 and mininum_day = 2020-01-01 and this interval parameter n_day_group_interval = 5, the function will return the following set:
2020-01-10
2020-01-08
2020-01-06
2020-01-04
2020-01-02
Following was my attempt that gives an syntax error at or near "end". Thank you for your help.
create function get_n_day_group_dates(most_recent_day date, minimum_day date, n_day_group_interval varchar)
returns table (
n_day_group_dates date
)
as $body$
begin
return query
SELECT
date_trunc('day'::text, dd.dd)::date AS n_day_group_dates
FROM
LATERAL generate_series(most_recent_day,
minimum_day,
'-'||n_day_group_interval||' day'::interval) dd(dd)
end;
$body$
language plpgsql;
You are overcomplicating things. LATERAL is not required and date_trunc('day', ..) is the same as casting to date.
To create an interval based on a variable, the easiest way is to use make_interval()
create function get_n_day_group_dates(most_recent_day date, minimum_day date, n_day_group_interval varchar)
returns table (n_day_group_dates date)
as $body$
SELECT g.dt::date
FROM generate_series(most_recent_day,
minimum_day,
make_interval(days => n_day_group_interval) as g(dt);
$body$
language sql;