I am trying to add a parameter inside a postgres function interval statement. Not sure how to add it...
Like follows:
CREATE OR REPLACE FUNCTION test_date(number integer)
RETURNS table (date_value date)
AS
$body$
BEGIN
RETURN QUERY (select (now() + interval '$1 days') ::date as column_value);
END;
$body$
language plpgsql;
I think you want:
RETURN QUERY select (now() + number * interval '1 day') ::date as column_value;
Expression number * interval '1 day') gives you an interval of number days.
You could also use make_interval():
RETURN QUERY select (now() + make_interval(days := number))::date as column_value;
You can add an integer to a date value directly, which adds the number of days. So if you use current_date no casting is needed.
CREATE OR REPLACE FUNCTION test_date(number integer)
RETURNS table (date_value date)
AS
$body$
BEGIN
RETURN QUERY select current_date + number;
END;
$body$
language plpgsql;
But I don't understand why you use a set-returning function for a single value. A scalar SQL function would be better in this case:
CREATE OR REPLACE FUNCTION test_date(number integer)
RETURNS date
AS
$body$
select current_date + number;
$body$
language sql
stable;