SQL 如何在postgresql中为可选参数编写函数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39896329/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to write function for optional parameters in postgresql?
提问by indu
My requirement is write optional parameters to a function.Parameters are optional sometimes i will add or i will not pass parameters to function.Can anyone help me how to write function.
我的要求是将可选参数写入函数。参数是可选的，有时我会添加或者我不会将参数传递给函数。任何人都可以帮助我如何编写函数。
I am writing like
我写得像
select *
from test
where field3 in ('value1','value2')
and ( is null or field1 = )
and ( is null or field2 = )
and ( is null or field3 = );
i am passing parameters to Query,But my output is not expected.when i pass all three parameters my output is correct,otherwise it is not expected output.
我正在将参数传递给 Query，但我的输出不是预期的。当我传递所有三个参数时，我的输出是正确的，否则不是预期的输出。
回答by a_horse_with_no_name
You can define optional parameters by supplying a default value.
您可以通过提供默认值来定义可选参数。
create function foo(p_one integer default null,
p_two integer default 42,
p_three varchar default 'foo')
returns text
as
$$
begin
return format('p_one=%s, p_two=%s, p_three=%s', p_one, p_two, p_three);
end;
$$
language plpgsql;
You can "leave out" parameters from the end, so foo(), foo(1)or foo(1,2)are valid. If you want to only supply a parameter that is not the first you have to use the syntax that specifies the parameter names.
您可以从末尾“省略”参数，因此foo(),foo(1)或foo(1,2)是有效的。如果您只想提供一个不是第一个参数，您必须使用指定参数名称的语法。
select foo();
returns: p_one=, p_two=42, p_three=foo
返回： p_one=, p_two=42, p_three=foo
select foo(1);
returns: p_one=1, p_two=42, p_three=foo
返回： p_one=1, p_two=42, p_three=foo
select foo(p_three => 'bar')
returns: p_one=, p_two=42, p_three=bar
返回： p_one=, p_two=42, p_three=bar
回答by bitifet
Apart of the VARIADICoption pointed by @a_horse_with_no_name, which is only a syntax sugar for passing an array with any number of elements of the same type, you can'tdefine a function with optional parameters because, in postgres, functions are identified not only by its name but also by its arguments and the types of them.
除了@a_horse_with_no_name指向的VARIADIC选项，它只是传递具有任意数量相同类型元素的数组的语法糖，您不能定义具有可选参数的函数，因为在 postgres 中，函数不仅被标识通过它的名字，还有它的参数和它们的类型。
That is: create function foo (int) [...]and create function foo (varchar) [...]will create differentfunctions.
即：create function foo (int) [...]并且create function foo (varchar) [...]会创建不同的功能。
Which is called when you execute, for example, select foo(bar)depends on bardata type itself. That is: if it is an integer, you will call the first one and if it is varchar, then second one will be called.
例如，select foo(bar)在执行时调用哪个取决于bar数据类型本身。也就是说：如果它是一个整数，你将调用第一个，如果它是 varchar，那么第二个将被调用。
More than that: if you execute, for example, select foo(now()), then a function not existsexception will be triggered.
更重要的是：如果执行，例如select foo(now())，那么函数不存在异常将被触发。
So, as I said, you can't implement functions with variable arguments, but you can implement multiple functions with the same name and distinct argument (an/or type) sets returning the same data type.
所以，正如我所说，你不能用可变参数实现函数，但你可以实现多个具有相同名称和不同参数（一个/或类型）集的函数，返回相同的数据类型。
If you (obviously) doesn't want to implement the function twice, the only thing you need to do is to implement a "master" function with all possible parameters and the others (which have fewer parameters) only calling the "master" one with default values for the non received parameters.
如果您（显然）不想两次实现该函数，那么您唯一需要做的就是实现一个具有所有可能参数的“主”函数，而其他（参数较少）仅调用“主”函数使用未接收参数的默认值。
回答by Kiry Meas
As an option, I got a function i tested with Navicat App:
CREATE OR REPLACE FUNCTION "public"."for_loop_through_query"(sponsor_name varchar default 'Save the Children')It generates me this. (Note: Please look at the parameter difference)
CREATE OR REPLACE FUNCTION "public"."for_loop_through_query"("sponsor_name" varchar='Save the Children'::character varying)
作为一个选项，我得到了一个我用 Navicat App 测试过的功能：
CREATE OR REPLACE FUNCTION "public"."for_loop_through_query"(sponsor_name varchar default 'Save the Children')它为我生成了这个。（注：请看参数差异）
CREATE OR REPLACE FUNCTION "public"."for_loop_through_query"("sponsor_name" varchar='Save the Children'::character varying)
CREATE OR REPLACE FUNCTION "public"."for_loop_through_query"("sponsor_name" varchar='Save the Children'::character varying)
RETURNS "pg_catalog"."void" AS $BODY$
DECLARE
rec RECORD;
BEGIN
FOR rec IN SELECT
companies."name" AS org_name,
"sponsors"."name" AS sponsor_name
FROM
"donor_companies"
JOIN "sponsors"
ON "donor_companies"."donor_id" = "sponsors"."id"
JOIN companies
ON "donor_companies"."organization_id" = companies."id"
WHERE
"public"."sponsors"."name" = sponsor_name
LOOP
RAISE NOTICE '%', rec.org_name;
END LOOP;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
