PostgreSQL СУЩЕСТВУЕТ в определяемой пользователем функции, всегда возвращающей true

Я написал простую пользовательскую функцию для проверки наличия строк, соответствующих некоторым условиям:

CREATE OR REPLACE FUNCTION is_instructor_specialized_in(eid INT, course_area VARCHAR(50))
RETURNS BOOLEAN AS $$
  SELECT EXISTS(SELECT 1 FROM Specializes s WHERE s.eid = eid AND s.name = course_area);
$$ LANGUAGE sql;

Я протестировал его с помощью следующего запроса:

SELECT is_instructor_specialized_in(2, 'Artificial Intelligence') as function_output, 
    EXISTS(SELECT 1 FROM Specializes s WHERE s.eid = 2 AND s.name = 'Artificial Intelligence') as ground_truth;

и функция дала неправильное значение true, когда она должна оцениваться как false (такой строки в таблице Specializes нет): изображение

На самом деле он всегда дает значение true. Я очень смущен. Есть ли причина, по которой это происходит?

Версия: PostgreSQL 13.2 на x86_64-apple-darwin19.6.0, скомпилированная Apple clang версии 11.0.3 (clang-1103.0.32.62), 64-разрядная


postgresql sql naming-conventions function-parameter
person Marcus Ong    schedule 03.04.2021    source источник
comment
Хм, я тоже этого ожидал, но почему-то получаю неправильные значения. Интересно, связано ли это с программой, которую я использую (pgAdmin 4).   -  person Marcus Ong    schedule 03.04.2021
comment
WHERE s.eid = eid ‹‹-- дайте вашему параметру функции другое имя (добавьте подчеркивание)   -  person wildplasser    schedule 03.04.2021

Ответы (1)


arrow_upward
1
arrow_downward

Как намекнул @wildplasser, параметр вашей функции eidимеет то же имя, что и столбец таблицы, что никогда не является хорошей идеей. В этом случае он молча сломал вашу функцию.

Неквалифицированный eid в WHERE s.eid = eid разрешается в столбец таблицы, а не в параметр функции, как вы, кажется, ожидаете. Таким образом, этот предикат оценивается как true для любого ненулевого ввода. Скрытая ошибка.

Руководство:

Если имя аргумента совпадает с именем любого столбца в текущей команде SQL внутри функции, имя столбца будет иметь приоритет. Чтобы переопределить это, укажите в имени аргумента имя самой функции, то есть function_name.argument_name. (Если это приведет к конфликту с полным именем столбца, снова преимущество будет иметь имя столбца. Вы можете избежать двусмысленности, выбрав другой псевдоним для таблицы в команде SQL.)

Жирный акцент мой.

Уточнение имени функции — неудобная крайняя мера. Избегайте проблемы, чтобы начать с однозначных имен параметров. Одно соглашение состоит в том, чтобы префикс параметров подчеркивать (_) и никогда не делать то же самое для столбцов таблицы:

CREATE OR REPLACE FUNCTION func_proper(_eid int, _course_area text)
  RETURNS boolean
  LANGUAGE sql STABLE PARALLEL SAFE AS
$func$
SELECT EXISTS(SELECT FROM specializes s WHERE s.eid = _eid AND s.name = _course_area);
$func$;

Или используйте позиционные ссылки на параметры $n для простого случая, подобного вашему. У вас все еще могут быть имена параметров для документации и для именованных вызовов функций:

CREATE OR REPLACE FUNCTION func_proper(_eid int, _course_area text)
  RETURNS boolean
  LANGUAGE sql STABLE PARALLEL SAFE AS
$func$
SELECT EXISTS(SELECT FROM specializes s WHERE s.eid = $1 AND s.name = $2);
$func$;

db‹›fiddle здесь

Поведение по умолчанию для одного и того же конфликта имен в функции PL/pgSQL заключается в возбуждении исключения, кстати. Видеть:

person Erwin Brandstetter    schedule 03.04.2021