如何编写函数的可选参数 postgreql？

我的要求是将可选参数写入函数。参数是可选的，有时我会添加或不会传递参数到函数。有人能帮我写函数吗。

我在写

select *
from test
where field3 in ('value1','value2')
and ($1 is null or field1 = $1)
and ($2 is null or field2 = $2)
and ($3 is null or field3 = $3);

我将参数传递给 Query，但是我的输出不是预期的。当我传递所有三个参数时，我的输出是正确的，否则它就不是预期的输出。

75706 次浏览
小开

Apart of the VARIADIC option 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't define 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.

That is: create function foo (int) [...] and create function foo (varchar) [...] will create different functions.

Which is called when you execute, for example, select foo(bar) depends on bar data 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.

More than that: if you execute, for example, select foo(now()), then a function not exists exception will be triggered.

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.

小开
最佳答案

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.

select foo();

returns: p_one=, p_two=42, p_three=foo

select foo(1);

returns: p_one=1, p_two=42, p_three=foo

select foo(p_three => 'bar')

returns: p_one=, p_two=42, p_three=bar

小开

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)

 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;

提交答案