Как в хранимую процедуру добавить проверку входных данных?
Доброго времени суток. У меня такая проблема. Есть задача: Разработать хранимые процедуры для добавления/изменения/удаления данных. В процедуры необходимо добавить проверки входных данных согласно таблице выше.(тип данных, условие NOT NULL, уникальность данных) В случае ввода некорректных данных выводить текст с читабельной ошибкой.
Есть код с процедурой:
CREATE PROCEDURE add_product (a INT, b TIMESTAMP, c VARCHAR(20), d VARCHAR(50), e INT, f INT)
LANGUAGE SQL
AS $$
INSERT INTO sales (ID, Sale_time, Pharmacy, product, Number_of_packages, Price)
VALUES (a, b, c, d, e, f);
$$;
Как в эту процедуру добавить проверку входных данных?? Заранее благодарю
Понимаете, мне не ясно, куда именно вставить это условие, и как должно выглядеть условие на проверку принадлежности к нужному типу данных. Те ресурсы, что вы мне прислали, я уже читала ранее, но они не работают. Постоянно ругается на ошибку синтаксиса.
Екатерина Шундеева,
CREATE PROCEDURE add_product (a INT, b TIMESTAMP, c VARCHAR(20), d VARCHAR(50), e INT, f INT)
LANGUAGE SQL
AS $$
INSERT INTO sales (ID, Sale_time, Pharmacy, product, Number_of_packages, Price)
VALUES (a, b, c, d, e, f);
IF a < 5 THEN
RAISE NOTICE 'перехватили ошибку 1';
$$;
ERROR: ОШИБКА: ошибка синтаксиса (примерное положение: "IF")
LINE 6: IF a < 5 THEN
^
Екатерина Шундеева, у вас в команде language указано sql, но тут нужно plpgsql.
IF THEN нужно закрывать END IF;
Условия вроде нужно заключать в круглые скобки:
IF (a<5) THEN
RAISE NOTICE 'hello world', now();
END IF;
Финально как-то так:
CREATE PROCEDURE add_product (a INT, b TIMESTAMP, c VARCHAR(20), d VARCHAR(50), e INT, f INT)
AS $$
BEGIN
INSERT INTO sales (ID, Sale_time, Pharmacy, product, Number_of_packages, Price)
VALUES (a, b, c, d, e, f);
IF (a < 5) THEN
RAISE NOTICE 'перехватили ошибку 1', now();
END IF;
END;
$$ LANGUAGE plpgsql;
Руслан ., Спасибо, в принципе работает, но все также остается вопрос: как поставить ограничение на тип данных? т.е. как мне сказать "если А не является типом INT, то выведи ошибку"?
Екатерина Шундеева, тип данных указан у каждого параметра процедуры.
Когда вы зовете процедуру, то либо в момент вызова все рухнет, из-за того что в процедуру не то подставили, либо процедура запустится.
В процедуре имеет смысл проверять допустимость переданных значений.
Руслан ., Руслан, я извиняюсь, что часто вас дёргаю.
Можно у вас ещё попросить советов?
Начнем сначала. Допустим, мне нужно написать хранимую процедуру (Postgresql, версия 11), которая добавляла бы данные в таблицу и проверяла корректные ли данные введены пользователем.
Т.е. что-то типо этого:
CREATE PROCEDURE add_product_4 (a INT, b TIMESTAMP, c VARCHAR(20), d VARCHAR(50), e INT, f INT)
AS $$
BEGIN
INSERT INTO sales (ID, Sale_time, Pharmacy, product, Number_of_packages, Price)
VALUES (a, b, c, d, e, f);
END;
$$ LANGUAGE 'plpgsql';
Потом данная процедура вызывается, например так:
add_product_4('2', '2019.11.12 12:23:24', 'Апрель', 'Цитрамон', '18', '105');
Пока все хорошо. НО, если в последнюю переменную вставить строковое значение, то клиент выдаст ошибку:
"ERROR: ОШИБКА: неверное значение для целого числа: "df"
LINE 2: ...', '2019.11.12 12:23:24', 'Апрель', 'Цитрамон', '18', 'df');
^
SQL-состояние: 22P02
Символ: 78"
Моя же задача, чтобы данная ошибка приобрела читабельный вид.
Я попробовала сделать это так:
CREATE PROCEDURE add_product_4 (a INT, b TIMESTAMP, c VARCHAR(20), d VARCHAR(50), e INT, f INT)
AS $$
BEGIN
INSERT INTO sales (ID, Sale_time, Pharmacy, product, Number_of_packages, Price)
VALUES (a, b, c, d, e, f);
EXCEPTION
WHEN invalid_text_representation THEN
RAISE NOTICE 'Ошибка: неверный тип переменной';
END;
$$ LANGUAGE 'plpgsql';
Т.е. зная код ошибки (22P02), я зашла на сайт: https://www.postgresql.org/docs/11/errcodes-append...
и узнала, как эта ошибка называется, после чего вставила это название после конструкции EXCEPTION WHEN. И применила конструкцию RAISE NOTICE для того. чтобы клиент теперь выводил заданный мною текст.
Но в итоге, после выполнения колла, ничего не меняется, клиент так и выводит дефолтный ответ (см. картинку).
Я смотрела видеоурок, где человек делает всё тоже самое и у него получается. я не могу понять, что я делаю не так?
Екатерина Шундеева, за postgres точно не скажу так глубоко не копал, но в других диалектах, таких как mssql или sybase, проверку типа нужно делать до вызова хранимки, например на форме вывода объекта, а вот допустимость значений при их правильном типе лучше в хранимке проверять. Тут скорее всего также, когда вы зовете хранимку с параметрами неправильного типа, хранимках не запускается на выполнение, ошибка возникает ещё до запуска ваших проверок, если нужно как-то это отловить нужно выполнять запуск хранимки с использованием конструкции типа try/catch, и в блоке catch отлавливать ошибки и давать им нормальное описание. Но скорее всего вам это тоже не поможет сильно, например если у вас есть хранимках и в ней 5 параметров типа int по коду ошибки вы не поймёте какой из них неверно передали.
Простой
