Postgresql Function with optional parameters

Question

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 ?

score 3 · Accepted Answer · 2019-11-28 15:13:21Z

3

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...

edited Nov 28, 2019 at 15:13

answered Nov 28, 2019 at 14:51

user330315

Sign up to request clarification or add additional context in comments.

6 Comments

Denis.A Over a year ago

So, no issue for positional parameters ?

user330315 Over a year ago

@Denis.A: what do you mean with "no issue"?

Denis.A Over a year ago

Inability to use the wanted syntax, i.e : function(,param2) or function(param1,) which is more convenient for understanding : "from the big bang to yesterday" : function(,'1 day') or "from yesterday to the big crunch" : function('1 day',)...

Belayer Over a year ago

You cannot do what you want if you want to omit param1. To omit optional positional parameters you can only eliminate those after (to the right of) the last specified one. @a_horse_with_no_name has provided the correct answer for answer: use named parameters. As far as "convenient for understanding" IMHO named parameters is vastly superior for that in any event and a trivial coding exercise.

Denis.A Over a year ago

Understood.Thanks to all.

|

Collectives™ on Stack Overflow

Postgresql Function with optional parameters

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related