CODEWITHSUNDEEP
sqlpostgresqldatetimesql-function
Daryn
Daryn

Reputation: 1609

Postgres add parameter to interval statement

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;

Upvotes: 1

Views: 1464

Answers (2)

user330315
user330315

Reputation:

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;

Upvotes: 0

GMB
GMB

Reputation: 222462

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;

Demo on DB Fiddle

Upvotes: 2

Related Questions