带有可选参数的 Postgresql 函数 [英] Postgresql Function with optional parameters

问题描述

有没有办法创建一个可以用可变数量的参数(逗号分隔，所以位置)调用的函数.例如，使用 function1(param1,param2) 调用此类函数并可能使用 function1(,param2) 或 function1(param1,) 调用它?我创建了一个带有默认参数的函数，但在调用它时出错:

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

它在不提供参数时有效 select * from idxi() 但在只提供一个时无效...

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

我哪里错了?

推荐答案

如果只想传第二个参数，按名字传:

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'); 

或按名称:

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

这篇关于带有可选参数的 Postgresql 函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！