CODEWITHSUNDEEP
postgresqlparametersstored-functions
Denis.A
Denis.A

Reputation: 191

Postgresql Function with optional parameters

Is there a way to create a function which can be called with a variable number of parameters (comma separated, so positional). For example, calling a such function with function1(param1,param2) and possibly calling it with function1(,param2) or function1(param1,) ? I've created a function with default parameters but I've errors when calling it :

select * from iDxi('3 days',) order by "Date" asc

ERROR:  syntax error at or near ")"
LINE 1: select * from iDxi('3 days',) order by "Date" asc

My function definition is like:

CREATE OR REPLACE FUNCTION public.idxi(
    mydated text DEFAULT '99 year'::text,
    mydatef text DEFAULT '-99 year'::text)
RETURNS TABLE...

It works when providing no args select * from idxi() but not when providing only one...

Where am I wrong ?

Upvotes: 2

Views: 4184

Answers (1)

user330315
user330315

Reputation:

If you only want to pass the second parameter, pass it by name:

select *
from idxi(mydatef => '-3 days');

If you only want to pass the first parameter, you can simply pass it by position (without a , after the parameter)

select *
from idxi('3 days');

Or by name as well:

select *
from idxi(mydated => '3 days');

Unrelated, but:

If you want to pass intervals to the function, you should declare the parameters of that type:

CREATE OR REPLACE FUNCTION public.idxi(
    mydated interval DEFAULT '99 year'::interval,
    mydatef interval DEFAULT '-99 year'::interval)
RETURNS TABLE...

Upvotes: 3

Related Questions