Блог

Я использую базу данных Postgres и хочу генерировать случайное student_no всякий раз, когда пользователь вставляет какие-либо записи в базу данных. Команда выглядит следующим образом:

 NEW.booking_no: = array_to_string (ARRAY (SELECT chr ((48   round (random () * 9)) :: integer) FROM generate_series (1,10)), '');
  

Структура моей таблицы следующая:

 Name Table : Student
(id Pk, 
 firstName varchar,
 lastName varchar, 
 student_no varchar, 
 location varchar, 
 age integer
)
  

Для удобства я реализую функции записи и триггеры с plpgsql следующим образом:

 //Create function
CREATE OR REPLACE FUNCTION student_no()
RETURNS TRIGGER AS
$$
BEGIN
    NEW.student_no := array_to_string(ARRAY(SELECT chr((48   round(random() * 9)) :: integer) FROM generate_series(1,10)), '');
RETURN NEW;
END
$$ LANGUAGE plpgsql;

//create trigger

CREATE TRIGGER student_no
BEFORE INSERT
ON public."Student"
FOR EACH ROW
EXECUTE PROCEDURE student_no();

//Data User Insert to database
INSERT INTO public."Student"(
    student_id, "firstName", "lastName", location, age)
    VALUES (2231, 'Join', 'David', 'UK',26);    
  

Когда я вставляю, он успешно создает и случайный student_no в моей базе данных. Это здорово. Но я хочу сравнить, если у студента одинаковое местоположение, student_no оно не должно дублироваться, если другое, оно может дублироваться. Если в том же местоположении и функции случайным образом совпадает один и тот же student_no , он должен создать другой случайный student_no. Я пишу код, который выглядит как :

 CREATE OR REPLACE FUNCTION student_no()
RETURNS TRIGGER AS
$$
DECLARE
canIUseIt boolean := false;
randomNumber BIGINT;
BEGIN
    //loop when random success
    WHILE ( not ( canIUseIt ) ) LOOP
    randomNumber  := array_to_string(ARRAY(SELECT chr((48   round(random() * 9)) :: integer) FROM generate_series(1,10)), '');
        //Get data from user input and compare with database. I not sure it true. If it wrong, please help me fix it.
        //New.location : data from user insert. I think
       // location  data from database
    SELECT location FROM Student WHERE location = NEW.location;
            IF NOT FOUND THEN
                canIUseIt = true;
            END IF;                                   
    END LOOP;
$$ LANGUAGE plpgsql;
 //If not duplicate, insert random number to database. And break loop.
 IF ( canIUseIt ) THEN
        RETURN NEW.booking_no: = array_to_string (ARRAY (SELECT chr ((48   round (random () * 9)) :: integer) FROM generate_series (1,10)), '');
END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER student_no
BEFORE INSERT
ON public."Student"
FOR EACH ROW
EXECUTE PROCEDURE student_no();
  

Но когда я выполняю команду excute Insert

   INSERT INTO public."Student"(
        student_id, "firstName", "lastName", location, age)
        VALUES (2231, 'A', 'Van Nguyen', 'DN',26);  
  

Это не работает. PostgresSQL выдает мне исключение :

ЗАПРОС: ВЫБЕРИТЕ местоположение ИЗ Student, ГДЕ location = NEW.контекст местоположения: функция PL / pgSQL student_no() строка 8 в инструкции SQL Состояние SQL: 42P01.

У меня вопрос :

• Как мне получить данные от пользователя ввода и сравнить с данными из базы данных. Если не совпадает, выполните команду random. Это то же значение из базы данных, оно должно вернуться и создать новое случайное.Пожалуйста, помогите мне, потому что я работаю в течение 1 дня и не справляюсь с проблемой.

Существует более одной проблемы

1. SELECT location FROM Student WHERE location = NEW.location; — PLpgSQL не позволяет выполнять запрос без некоторого целевого результата. Для SELECT требуется INTO предложение. Если вам не нужно сохранять результат, используйте PERFORM оператор или лучше (в данном случае), используйте вместо него предикат EXISTS So:

    -- bad
   SELECT location FROM student WHERE location = NEW.location;
   IF NOT FOUND THEN
      can_i_use_it := true;
   END IF; 
   
   -- can works
   PERFORM location FROM student WHERE location = NEW.location;
   IF NOT FOUND THEN
     can_i_use_it := true;
   END IF;
   
   -- better
   IF NOT EXISTS(SELECT * FROM student WHERE ...) THEN
     can_i_use_it := true;
   END IF;              
   
   -- good
   can_i_use_it := EXISTS(SELECT * FROM Student WHERE location = NEW.location)
     

2. но этого метода недостаточно для защиты вас от состояния гонки. В любое время базой данных может воспользоваться больше пользователей. Новее, вы видите текущие последние данные. В любой момент вы можете увидеть только некоторый снимок — и без блокировки или UNIQUE индексирования запроса like IF EXISTS(some query) THEN не является хорошей защитой от дубликатов строк. Это невозможно сделать хорошо без более агрессивной блокировки со стороны триггеров. Ваш пример — хороший пример того, как не использовать триггеры. Используйте эту логику в явно вызываемой функции (например, в plpgsql тоже), но не в триггере. Для этого случая это плохое место.

3. PLpgSQL не чувствителен к регистру — не используйте верблюжью нотацию. SQL не чувствителен к регистру — не используйте верблюжью нотацию и не учитывайте регистр идентификаторов SQL, таких как «LastName» — это самый короткий путь в психиатрическую больницу.