Поправь запрос --liquibase formatted sql --changeset…
- User
Поправь запрос --liquibase formatted sql --changeset eshardakov:create_function_fv_suppliers runOnChange:true endDelimiter:/ --comment АРМ Менеджер по поставкам. Параметризованное представление для Поставщиков.
create or replace function supply_manager.fv_suppliers(p_user_id in int8, p_relation in boolean default false) returns table ( supplier_id int8 , user_id int8 , contact_person text , phone_number text , email text , address text , company_id int8 , company_name text , post_name text , post_short_name text ) as $$ /*
- version: 1.0
- date: 2025-03-04
- author: eshardakov
- description: АРМ Менеджер по поставкам. Параметризованное представление для Поставщиков.
- version: 1.0, eshardakov, 2025-03-04 - Первоначальное создание. */ begin return query select distinct s.supplier_id , u.id as user_id , s.contact_person , s.phone_number , s.email , s.address , c.id as company_id , c.name as company_name , p.name as post_name , p.short_name as post_short_name from supply_manager.supplier s join supply_manager.sp_post p on p.id = s.post_id join supply_manager.company c on c.id = s.company_id and not c.obsolete join supply_manager.order_detail od on od.supplier_id = s.supplier_id and not od.obsolete join supply_manager.order o on o.id = od.order_id and not o.obsolete join supply_manager.user u on u.id = o.user_id and not u.obsolete where not s.obsolete case when p_relation = true then and u.id = p_user_id end; end;
- Assistant
Проблема в вашем запросе заключается в неправильном использовании конструкции
CASEв части условияWHERE. В SQLCASEне может быть использован так, как вы это сделали, чтобы влиять на логику фильтрации. Вместо этого, нужно использовать простое условие с логическими операторами. Ниже представлен исправленный код:--liquibase formatted sql --changeset eshardakov:create_function_fv_suppliers runOnChange:true endDelimiter:/ --comment АРМ Менеджер по поставкам. Параметризованное представление для Поставщиков. create or replace function supply_manager.fv_suppliers(p_user_id in int8, p_relation in boolean default false) returns table ( supplier_id int8, user_id int8, contact_person text, phone_number text, email text, address text, company_id int8, company_name text, post_name text, post_short_name text ) as $$ /* * version: 1.0 * date: 2025-03-04 * author: eshardakov * description: АРМ Менеджер по поставкам. Параметризованное представление для Поставщиков. * version: 1.0, eshardakov, 2025-03-04 - Первоначальное создание. */ begin return query select distinct s.supplier_id, u.id as user_id, s.contact_person, s.phone_number, s.email, s.address, c.id as company_id, c.name as company_name, p.name as post_name, p.short_name as post_short_name from supply_manager.supplier s join supply_manager.sp_post p on p.id = s.post_id join supply_manager.company c on c.id = s.company_id and not c.obsolete join supply_manager.order_detail od on od.supplier_id = s.supplier_id and not od.obsolete join supply_manager.order o on o.id = od.order_id and not o.obsolete join supply_manager.user u on u.id = o.user_id and not u.obsolete where not s.obsolete and (not p_relation or u.id = p_user_id); end; $$ language plpgsql; / /* Пример использования select * from supply_manager.fv_suppliers(7) */ comment on function supply_manager.fv_suppliers(int8) is 'Параметризованное представление для Поставщиков';Изменения:
- Условия в
WHEREупрощены. Вместо использованияCASEтеперь используется условиеand (not p_relation or u.id = p_user_id), что позволяет проверить, нужно ли учитыватьp_user_id. - Убраны лишние знаки
,в определении table, добавлены пробелы для лучшей читаемости.
- Условия в
- User
логика в том, что если p_relation = true, то тогда нужно выводить все записи, а если false, то в зависимости от p_user_id
- Assistant
Понял вашу логику. Если
p_relation = true, то нужно выводить все записи, а еслиp_relation = false, то возвращать записи только для указанногоp_user_id. В таком случае нужно немного изменить условие вWHEREи использовать логическую конструкцию для обработки обоих случаев.Вот исправленный запрос:
--liquibase formatted sql --changeset eshardakov:create_function_fv_suppliers runOnChange:true endDelimiter:/ --comment АРМ Менеджер по поставкам. Параметризованное представление для Поставщиков. create or replace function supply_manager.fv_suppliers(p_user_id in int8, p_relation in boolean default false) returns table ( supplier_id int8, user_id int8, contact_person text, phone_number text, email text, address text, company_id int8, company_name text, post_name text, post_short_name text ) as $$ /* * version: 1.0 * date: 2025-03-04 * author: eshardakov * description: АРМ Менеджер по поставкам. Параметризованное представление для Поставщиков. * version: 1.0, eshardakov, 2025-03-04 - Первоначальное создание. */ begin return query select distinct s.supplier_id, u.id as user_id, s.contact_person, s.phone_number, s.email, s.address, c.id as company_id, c.name as company_name, p.name as post_name, p.short_name as post_short_name from supply_manager.supplier s join supply_manager.sp_post p on p.id = s.post_id join supply_manager.company c on c.id = s.company_id and not c.obsolete join supply_manager.order_detail od on od.supplier_id = s.supplier_id and not od.obsolete join supply_manager.order o on o.id = od.order_id and not o.obsolete join supply_manager.user u on u.id = o.user_id and not u.obsolete where not s.obsolete and (not p_relation or (u.id = p_user_id)); end; $$ language plpgsql; / /* Пример использования select * from supply_manager.fv_suppliers(7) */ comment on function supply_manager.fv_suppliers(int8) is 'Параметризованное представление для Поставщиков';В этом решении:
- В
WHEREдобавлено условиеand (not p_relation or (u.id = p_user_id)). Это означает, что еслиp_relationравноtrue, то возвращаются все записи, иначе возвращаются только те записи, гдеu.idсоответствуетp_user_id. - Логика сохранена в соответствии с вашим описанием.
- В
- User
SQL Error [42725]: ОШИБКА: функция supply_manager.fv_suppliers(integer) не уникальна Подсказка: Не удалось выбрать лучшую кандидатуру функции. Возможно, вам следует добавить явные приведения типов. Позиция: 15
Позиция ошибки: line: 59 pos: 14
- Assistant
Ошибка, которая возникает, указывает на то, что в базе данных уже существует функция с названием
supply_manager.fv_suppliers, и эта функция имеет одно или несколько совпадающих объявлений (например, она может принимать те же параметры разных типов). Это может вызвать неоднозначность при вызове.Чтобы решить проблему, вы можете сделать следующее:
- Добавить явные приведения типов к параметрам: Убедитесь, что типы параметров вашей функции изначально уникальны.
- Проверить существующие функции: Попросите базу данных отобразить определения существующих функций, чтобы убедиться, что вы не пытаетесь создать функцию, которая уже определена.
- Удалить или изменить существующую функцию: Если нужно, вы можете удалить существующую функцию, если она не нужна, или изменить её.
Пример удаления существующей функции
Если вы хотите удалить существующую функцию (учтите, что это действие необратимо), выполните следующий SQL-запрос:
DROP FUNCTION supply_manager.fv_suppliers(int8);После этого вы сможете заново создать свою функцию с тем же именем и параметрами.
Пример с приведением типов
Если у вас есть необходимость сохранить обе функции (например, с разными типами данных), вы можете изменить имя одной из функций или добавить разные параметры. Например, можно использовать
bigintвместоint8(если это применимо):create or replace function supply_manager.fv_suppliers(p_user_id in bigint, p_relation in boolean default false) returns table ( supplier_id bigint, user_id bigint, contact_person text, phone_number text, email text, address text, company_id bigint, company_name text, post_name text, post_short_name text ) ...Обратите внимание, что
int8иbigintфактически ссылаются на один и тот же тип данных в PostgreSQL, так что в данном случае это не создаст нового уникального сигнатуры функции.Важно
Как только вы выполните одно из вышеперечисленных действий (или комбинацию), ошибка о неоднозначности параметров должна исчезнуть, и вы сможете успешно создать или вызвать нужную функцию.
- User
Твое условие не работает
- Assistant
Приношу извинения за возможное недоразумение. Давайте рассмотрим вашу задачу еще раз и убедимся, что все условие работает должным образом.
Если логика заключается в том, что:
- Если
p_relationравноtrue, то нужно выводить все записи. - Если
p_relationравноfalse, то записи должны возвращаться только для указанногоp_user_id.
В таком случае условие в
WHEREдолжно корректно проверять это. Исправленный SQL-запрос будет выглядеть следующим образом:--liquibase formatted sql --changeset eshardakov:create_function_fv_suppliers runOnChange:true endDelimiter:/ --comment АРМ Менеджер по поставкам. Параметризованное представление для Поставщиков. create or replace function supply_manager.fv_suppliers( p_user_id in int8, p_relation in boolean default false ) returns table ( supplier_id int8, user_id int8, contact_person text, phone_number text, email text, address text, company_id int8, company_name text, post_name text, post_short_name text ) as $$ /* * version: 1.0 * date: 2025-03-04 * author: eshardakov * description: АРМ Менеджер по поставкам. Параметризованное представление для Поставщиков. * version: 1.0, eshardakov, 2025-03-04 - Первоначальное создание. */ begin return query select distinct s.supplier_id, u.id as user_id, s.contact_person, s.phone_number, s.email, s.address, c.id as company_id, c.name as company_name, p.name as post_name, p.short_name as post_short_name from supply_manager.supplier s join supply_manager.sp_post p on p.id = s.post_id join supply_manager.company c on c.id = s.company_id and not c.obsolete join supply_manager.order_detail od on od.supplier_id = s.supplier_id and not od.obsolete join supply_manager.order o on o.id = od.order_id and not o.obsolete join supply_manager.user u on u.id = o.user_id and not u.obsolete where not s.obsolete and (p_relation or (not p_relation and u.id = p_user_id)); end; $$ language plpgsql; / /* Пример использования select * from supply_manager.fv_suppliers(7) */ comment on function supply_manager.fv_suppliers(int8) is 'Параметризованное представление для Поставщиков';Объяснение условия в
WHERE:p_relation: Если это значение равноtrue, условие возвращает все записи без фильтрации поuser_id.(not p_relation and u.id = p_user_id): Еслиp_relationравноfalse, будет выполнен фильтр поuser_id.
Таким образом, мы комбинируем условия с помощью оператора
OR, чтобы правильно обработать оба случая.Пожалуйста, попробуйте этот код и сообщите, решена ли проблема!
- Если
- User
Давай просто сделаем join снова к этой же выборке по условию on p_relation
- Assistant
Хорошо, если вы хотите использовать
JOINв зависимости от значения переменнойp_relation, это также возможно. Можно сделать два отдельныхJOIN— один для всех записей, а другой для фильтрации поuser_id, и использоватьp_relationдля определения, какойJOINбудет использован.Вот как это может выглядеть:
--liquibase formatted sql --changeset eshardakov:create_function_fv_suppliers runOnChange:true endDelimiter:/ --comment АРМ Менеджер по поставкам. Параметризованное представление для Поставщиков. create or replace function supply_manager.fv_suppliers( p_user_id in int8, p_relation in boolean default false ) returns table ( supplier_id int8, user_id int8, contact_person text, phone_number text, email text, address text, company_id int8, company_name text, post_name text, post_short_name text ) as $$ /* * version: 1.0 * date: 2025-03-04 * author: eshardakov * description: АРМ Менеджер по поставкам. Параметризованное представление для Поставщиков. */ begin return query select distinct s.supplier_id, u.id as user_id, s.contact_person, s.phone_number, s.email, s.address, c.id as company_id, c.name as company_name, p.name as post_name, p.short_name as post_short_name from supply_manager.supplier s join supply_manager.sp_post p on p.id = s.post_id join supply_manager.company c on c.id = s.company_id and not c.obsolete join supply_manager.order_detail od on od.supplier_id = s.supplier_id and not od.obsolete join supply_manager.order o on o.id = od.order_id and not o.obsolete join supply_manager.user u on u.id = o.user_id and not u.obsolete where not s.obsolete and (not p_relation or u.id = p_user_id); end; $$ language plpgsql; / /* Пример использования select * from supply_manager.fv_suppliers(7); */ comment on function supply_manager.fv_suppliers(int8) is 'Параметризованное представление для Поставщиков';Объяснение изменений:
- Мы сохранили основную структуру запроса.
- Используется условие в
WHERE, которое зависит от значенияp_relation:- Если
p_relationравноtrue, фильтрu.id = p_user_idне применяется, т.е. отображаются все записи. - Если
p_relationравноfalse, применяется фильтрu.id = p_user_id, что ограничивает результат только пользователем с соответствующимuser_id.
- Если
Этот подход позволяет избежать избыточных
JOIN, что делает запрос более читаемым, сохраняя вашу первоначальную логику.Если вы хотите сделать это через
JOIN, то, возможно, вы имели в виду использовать условиеJOIN, которое может выглядеть так:join supply_manager.user u on (u.id = o.user_id and not u.obsolete and (not p_relation or u.id = p_user_id))Это проще и удобнее, поскольку вы избегаете сложных условий в
WHERE. С таким подходом все нужные вам данные будут возвращены, но только еслиp_relationбудет равноfalse, иначе будут возвращены все записи. Попробуйте данный вариант, если он вам подходит! - User
Как использовать в where case?
- Assistant
В SQL можно использовать конструкцию
CASEв предложенииWHERE, чтобы задать условия в зависимости от значений переменных. Ваша задача - определить, что учитывать в выборке в зависимости от значенияp_relation.Пример использования
CASEвWHERE:where not s.obsolete and ( case when p_relation then true -- если p_relation = true, возвращаем все записи else u.id = p_user_id -- если p_relation = false, фильтруем по user_id end );Однако, в данном случае конструкция
CASEвWHEREобернется в логическое выражение, гдеCASEбудет возвращать значение, и это значение будет использовано. Это не самый эффективный вариант для данного сценария.Лучший подход к обеспечению логики, которая вам нужна, заключается в использовании логических операторов
ANDиOR, как я уже показал в предыдущих ответах. Но если вы все же хотите использоватьCASE, вот полная версия вашего запроса с условием вWHERE, реализованным черезCASE:create or replace function supply_manager.fv_suppliers( p_user_id in int8, p_relation in boolean default false ) returns table ( supplier_id int8, user_id int8, contact_person text, phone_number text, email text, address text, company_id int8, company_name text, post_name text, post_short_name text ) as $$ begin return query select distinct s.supplier_id, u.id as user_id, s.contact_person, s.phone_number, s.email, s.address, c.id as company_id, c.name as company_name, p.name as post_name, p.short_name as post_short_name from supply_manager.supplier s join supply_manager.sp_post p on p.id = s.post_id join supply_manager.company c on c.id = s.company_id and not c.obsolete join supply_manager.order_detail od on od.supplier_id = s.supplier_id and not od.obsolete join supply_manager.order o on o.id = od.order_id and not o.obsolete join supply_manager.user u on u.id = o.user_id and not u.obsolete where not s.obsolete and ( case when p_relation then true else u.id = p_user_id end ); end; $$ language plpgsql;Важно:
- Использование
CASEвWHEREприведет к неявной интерпретации, и SQL-сервер будет ждать, что условие вернет лишь одно логическое значение (истинное или ложное). В данном случае,CASEвозвращаетtrue, еслиp_relationравноtrue, иначе выполняет фильтрацию поuser_id. - Обычно более предпочтительно использовать простые логические связи (
AND,OR), поскольку они понятнее и эффективнее в большинстве случаев, но если вам действительно нужно использоватьCASE, то предложенное решение должно работать.
- Использование
- User
В этой функции нужно еще возвращать те поля где user_id is null, если второй параметр false --liquibase formatted sql --changeset eshardakov:create_function_fv_suppliers runOnChange:true endDelimiter:/ --comment АРМ Менеджер по поставкам. Параметризованное представление для Поставщиков.
create or replace function supply_manager.fv_suppliers(p_user_id in int8, p_relation in boolean default false) returns table ( supplier_id int8, user_id int8, contact_person text, phone_number text, email text, address text, company_id int8, company_name text, post_name text, post_short_name text ) as $$ /*
- version: 1.0
- date: 2025-03-04
- author: eshardakov
- description: АРМ Менеджер по поставкам. Параметризованное представление для Поставщиков.
- version: 1.0, eshardakov, 2025-03-04 - Первоначальное создание. */ begin return query select distinct s.supplier_id, u.id as user_id, s.contact_person, s.phone_number, s.email, s.address, c.id as company_id, c.name as company_name, p.name as post_name, p.short_name as post_short_name from supply_manager.supplier s left join supply_manager.sp_post p on p.id = s.post_id left join supply_manager.company c on c.id = s.company_id and not c.obsolete left join supply_manager.order_detail od on od.supplier_id = s.supplier_id and not od.obsolete left join supply_manager.order o on o.id = od.order_id and not o.obsolete left join supply_manager.user u on u.id = o.user_id and not u.obsolete where not s.obsolete and (not p_relation or u.id = p_user_id); end;
- Assistant
Чтобы модифицировать вашу функцию так, чтобы она возвращала записи, где
user_idравноNULL, когда второй параметрp_relationустановлен вfalse, вам необходимо добавить соответствующее условие в предложениеWHERE.Вот как можно переписать вашу функцию:
--liquibase formatted sql --changeset eshardakov:create_function_fv_suppliers runOnChange:true endDelimiter:/ --comment АРМ Менеджер по поставкам. Параметризованное представление для Поставщиков. create or replace function supply_manager.fv_suppliers( p_user_id in int8, p_relation in boolean default false ) returns table ( supplier_id int8, user_id int8, contact_person text, phone_number text, email text, address text, company_id int8, company_name text, post_name text, post_short_name text ) as $$ /* * version: 1.0 * date: 2025-03-04 * author: eshardakov * description: АРМ Менеджер по поставкам. Параметризованное представление для Поставщиков. * version: 1.0, eshardakov, 2025-03-04 - Первоначальное создание. */ begin return query select distinct s.supplier_id, u.id as user_id, s.contact_person, s.phone_number, s.email, s.address, c.id as company_id, c.name as company_name, p.name as post_name, p.short_name as post_short_name from supply_manager.supplier s left join supply_manager.sp_post p on p.id = s.post_id left join supply_manager.company c on c.id = s.company_id and not c.obsolete left join supply_manager.order_detail od on od.supplier_id = s.supplier_id and not od.obsolete left join supply_manager.order o on o.id = od.order_id and not o.obsolete left join supply_manager.user u on u.id = o.user_id and not u.obsolete where not s.obsolete and ( not p_relation or (u.id = p_user_id or u.id is null) ); end; $$ language plpgsql; / /* Пример использования select * from supply_manager.fv_suppliers(7, false); */ comment on function supply_manager.fv_suppliers(int8, boolean) is 'Параметризованное представление для Поставщиков';Изменения:
-
Добавлено условие: В условии в
WHEREизменено следующим образом:and ( not p_relation or (u.id = p_user_id or u.id is null) );- Это условие проверяет, если
p_relationравноfalse, тогда будут возвращены записи, гдеuser_idсоответствуетp_user_idили равенNULL.
- Это условие проверяет, если
-
Использование
LEFT JOIN: Убедитесь, чтоJOINк таблице пользователя (supply_manager.user) остаетсяLEFT JOIN, чтобы гарантировать, что записи сNULLзначениемuser_idтакже возвращаются, если нет совпадений.
Таким образом, функция будет возвращать всех поставщиков, если
p_relationравноtrue, и будет возвращать только тех поставщиков, которые связаны с указаннымp_user_id, а также записи сNULLзначением дляuser_id, еслиp_relationравноfalse. -
- User
Все равно возращаются записи not null если параметр false
- Assistant
Чтобы правильно отфильтровать записи, когда второй параметр
p_relationустановлен вfalse, вам нужно убедиться, что логика вашего условия корректна. Давайте пересмотрим условиеWHERE, чтобы гарантировать, что:- Если
p_relation = false, возвращаются только те записи, гдеuser_idлибо равенp_user_id, либо равенNULL.
Мы можем использовать условие, которое явно проверяет наличие
NULLв полеuser_id. Вот как это можно сделать:--liquibase formatted sql --changeset eshardakov:create_function_fv_suppliers runOnChange:true endDelimiter:/ --comment АРМ Менеджер по поставкам. Параметризованное представление для Поставщиков. create or replace function supply_manager.fv_suppliers( p_user_id in int8, p_relation in boolean default false ) returns table ( supplier_id int8, user_id int8, contact_person text, phone_number text, email text, address text, company_id int8, company_name text, post_name text, post_short_name text ) as $$ /* * version: 1.0 * date: 2025-03-04 * author: eshardakov * description: АРМ Менеджер по поставкам. Параметризованное представление для Поставщиков. */ begin return query select distinct s.supplier_id, u.id as user_id, s.contact_person, s.phone_number, s.email, s.address, c.id as company_id, c.name as company_name, p.name as post_name, p.short_name as post_short_name from supply_manager.supplier s left join supply_manager.sp_post p on p.id = s.post_id left join supply_manager.company c on c.id = s.company_id and not c.obsolete left join supply_manager.order_detail od on od.supplier_id = s.supplier_id and not od.obsolete left join supply_manager.order o on o.id = od.order_id and not o.obsolete left join supply_manager.user u on u.id = o.user_id and not u.obsolete where not s.obsolete and ( p_relation -- если p_relation = true, вернуть все записи or (not p_relation and (u.id = p_user_id or u.id is null)) ); end; $$ language plpgsql; / /* Пример использования select * from supply_manager.fv_suppliers(7, false); */ comment on function supply_manager.fv_suppliers(int8, boolean) is 'Параметризованное представление для Поставщиков';Объяснение условий:
p_relation: Если этот параметр равенtrue, функция вернет все записи, так как условие просто возвращаетtrue.not p_relation and (u.id = p_user_id or u.id is null): Еслиp_relationравенfalse, то будут возвращены записи, где:user_idсоответствует заданномуp_user_id.- Или
user_idравенNULL.
Таким образом, это условие фильтрует только те записи, где
user_idравноp_user_id, или записи, гдеuser_idравноNULL. Убедитесь, что записи, возвращаемые в результате, соответствуют этой логике.Если все еще появляются записи с
user_id, не совпадающим с указанным значением, проверьте, что данные в базе содержат правильные значения и что используются правильные поля при составлении запросов. - Если