#sql #postgresql #plpgsql
#sql #postgresql #plpgsql
Вопрос:
Мое требование — записать необязательные параметры в функцию.Параметры являются необязательными, иногда я добавляю или не передаю параметры функции.Кто-нибудь может мне помочь, как написать функцию.
Я пишу, как
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);
я передаю параметры в запрос, но мой вывод не ожидается.когда я передаю все три параметра, мой вывод корректен, в противном случае это не ожидаемый результат.
Комментарии:
1. Я хочу использовать и, или условия в моей функции.
Ответ №1:
Вы можете определить необязательные параметры, указав значение по умолчанию.
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;
Вы можете «пропустить» параметры с конца, so foo()
foo(1)
или foo(1,2)
являются допустимыми. Если вы хотите указать только параметр, который не является первым, вы должны использовать синтаксис, который задает имена параметров.
select foo();
ВОЗВРАТ: p_one=, p_two=42, p_three=foo
select foo(1);
ВОЗВРАТ: p_one=1, p_two=42, p_three=foo
select foo(p_three => 'bar')
ВОЗВРАТ: p_one=, p_two=42, p_three=bar
Комментарии:
1. могу ли я использовать оператор select вместо формата возврата в приведенной выше функции?. Если я использую оператор select, я получаю сообщение об ошибке типа «запрос не имеет назначения для результирующих данных»
2. @indu: это совершенно другой вопрос, на который дан ответ в руководстве: postgresql.org/docs/current/static /…
Ответ №2:
Помимо опции VARIADIC, указанной @a_horse_with_no_name, которая является всего лишь синтаксическим сахаром для передачи массива с любым количеством элементов одного и того же типа, вы не можете определить функцию с необязательными параметрами, потому что в postgres функции идентифицируются не только по имени, но и по аргументам и типамиз них.
То есть: create function foo (int) [...]
и create function foo (varchar) [...]
создаст разные функции.
Которая вызывается при выполнении, например, select foo(bar)
зависит от самого типа данных bar . То есть: если это целое число, вы вызовете первое, а если это varchar, то будет вызван второй.
Более того: если вы выполните, например, select foo(now())
, то будет вызвано исключение function not exists .
Итак, как я уже сказал, вы не можете реализовать функции с переменными аргументами, но вы можете реализовать несколько функций с одинаковыми именами и различными наборами аргументов (an / или type), возвращающими один и тот же тип данных.
Если вы (очевидно) не хотите реализовывать функцию дважды, единственное, что вам нужно сделать, это реализовать «главную» функцию со всеми возможными параметрами, а остальные (у которых меньше параметров) вызывают только «главную» со значениями по умолчанию для неполученных параметров.
Ответ №3:
В качестве опции у меня есть функция, которую я тестировал в приложении Navicat: 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;