Почему в этой функции Postgres необходимо явное имя таблицы?
Я впервые использую Postgres, и меня смущает «неоднозначная» ошибка относительно моих функций.
CREATE TABLE "user" (
user_id VARCHAR(40) PRIMARY KEY,
email VARCHAR(254),
phone_number VARCHAR(15) NOT NULL,
username VARCHAR(15) NOT NULL,
display_name VARCHAR(15),
rank_points INT NOT NULL,
created_on TIMESTAMP DEFAULT (TIMEZONE('UTC', NOW()))
);
У меня есть следующая функция для получения пользователей по идентификатору, телефону или имени пользователя.
CREATE OR REPLACE FUNCTION user_get(user_id_param VARCHAR(40) DEFAULT NULL, phone_number_param VARCHAR(15) DEFAULT NULL, username_param VARCHAR(15) DEFAULT NULL)
RETURNS TABLE(user_id VARCHAR(40), email VARCHAR(254), phone_number VARCHAR(15), username VARCHAR(15), display_name VARCHAR(15), rank_points INT, created_on TIMESTAMP) AS
$$
BEGIN
RETURN QUERY
SELECT "user".user_id, "user".email, "user".phone_number, "user".username, "user".display_name, "user".rank_points, "user".created_on
FROM "user"
WHERE
(user_id_param IS NOT NULL AND "user".user_id = user_id_param) OR
(phone_number_param IS NOT NULL AND "user".phone_number = phone_number_param) OR
(username_param IS NOT NULL AND "user".username = username_param);
END;
$$
LANGUAGE plpgsql;
Мой вопрос касается требования "user." в SELECT, а также причины WHERE. Без него получаю ошибку:
ОШИБКА: ссылка на столбец «user_id» неоднозначна.
Где двусмысленность? Это из-за user_id в предложении RETURNS TABLE?
Ответы 1
Где двусмысленность? Это из-за
user_idв предложенииRETURNS TABLE?
Да, все столбцы, перечисленные там, считаются параметрами OUT, которые видны в любом SQL-запросе внутри функции, поэтому конфликтуют с именами столбцов, если они не указаны. Видеть:
- Как вернуть результат SELECT внутри функции в PostgreSQL?
- Область действия имен столбцов, псевдонимов и параметров OUT в функции PL/pgSQL
Тем не менее, ваша функция может быть намного проще:
CREATE OR REPLACE FUNCTION user_get(user_id_param text = NULL, phone_number_param text = NULL, username_param text = NULL)
RETURNS SETOF "user"
LANGUAGE sql AS
$func$
SELECT *
FROM "user" u
WHERE u.user_id = user_id_param
OR u.phone_number = phone_number_param
OR u.username = username_param;
$func$;
Это делает более простая функция SQL. Если вам вообще нужна функция для этого.
Тип возвращаемого значения точно соответствует типу строки таблицы, поэтому вы можете его использовать. RETURNS SETOF "user" вместо RETURNS TABLE (...) (Обратите внимание, что это вводит функциональную зависимость от типа таблицы!) Это также устраняет наблюдаемые конфликты. И это один из редких случаев, когда SELECT * имеет смысл при возврате из функции, поскольку тип возвращаемого значения буквально соответствует типу строки по определению.
Если вам нужно уточнить имена столбцов в таблице (что для начала не является плохой идеей!), рассмотрите возможность использования короткого псевдонима столбца для удобства чтения (u в моем случае).
Я сомневаюсь, что вам нужно значение по умолчанию NULL для параметров IN.
И дополнительно проверять null не нужно. Проверка с помощью оператора равенства = неявно исключает null, поскольку null по сравнению с чем-либо возвращает null, никогда true. Видеть:
Кроме того, не используйте полностью зарезервированное слово, например user, в качестве идентификатора.
«Если вам вообще нужна функция для этого». В моей повседневной работе мы используем MS SQL Server и имеем SP для каждого взаимодействия с таблицей. Мой план состоял в том, чтобы сделать то же самое здесь. Разве это не рекомендуемый шаблон для postgres?
Все это зависит. Проще говоря, я не вижу большой возможной выгоды, но вижу большие накладные расходы.
Читайте Подстановка переменных.