CODEWITHSUNDEEP
postgresqldateparametersintervals
CyberPunk
CyberPunk

Reputation: 1447

Pass date intervals as function parameters

I have a database function as below:

drop function test(month_interval text)
create or replace function test (month_interval text) returns date as
$$
select ('2020-07-01'::date - interval month_interval)::date;
$$ language sql;


   select * from test('2 months')

I have a scenario where I want to dynamically compute month intervals and want to have one database query that can be used by passing month intervals as function parameters. However when i do this it gives me the following error :

ERROR: syntax error at or near "month_interval"

Upvotes: 1

Views: 498

Answers (1)

user330315
user330315

Reputation:

You could cast the text to an interval:

create or replace function test (month_interval text) returns date as
$$
select ('2020-07-01'::date - month_interval::interval)::date;
$$ language sql;

select test('2 months');

But why not pass an interval directly?

create or replace function test (month_interval interval) returns date as
$$
select ('2020-07-01'::date - month_interval)::date;
$$ language sql;

select test(interval '2 months');

Alternatively you can pass the number of months, then use make_interval:

create or replace function test (num_months int) returns date as
$$
select ('2020-07-01'::date - make_interval(months => num_months))::date;
$$ language sql;

select test(2);

Upvotes: 2

Related Questions