title: How to compare the value the user enters with the data in the database when performing some random use of plpgsql
body: <p>I use the Postgres database and I want to generate random student_no whenever the user inserts any records into the database. The comand is as follows:</p>
  
  <pre><code>NEW.booking_no: = array_to_string (ARRAY (SELECT chr ((48 + round (random () * 9)) :: integer) FROM generate_series (1,10)), '');
  </code></pre>
  
  <p>My table structure is as follows: </p>
  
  <pre><code>Name Table : Student
  (id Pk, 
   firstName varchar,
   lastName varchar, 
   student_no varchar, 
   location varchar, 
   age integer
  )
  </code></pre>
  
  <p>For convenience, I implement writing functions and triggers with plpgsql as follows:</p>
  
  <pre><code>//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);    
  </code></pre>
  
  <p>When i insert, it success create and random student_no in my database. It great. But I want compare if Student same location, student_no it must not duplicate, if different it can duplication. If same location and function random same student_no , it must create another random student_no. I write code look like :</p>
  
  <pre><code>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();
  </code></pre>
  
  <p>But when i excute command Insert</p>
  
  <pre><code>  INSERT INTO public."Student"(
          student_id, "firstName", "lastName", location, age)
          VALUES (2231, 'A', 'Van Nguyen', 'DN',26);  
  </code></pre>
  
  <p>It's not working. PostgresSQL throw me exception : </p>
  
  <blockquote>
    <p>QUERY:  SELECT location FROM Student WHERE location = NEW.location
    CONTEXT:  PL/pgSQL function student_no() line 8 at SQL statement SQL
    state: 42P01.</p>
  </blockquote>
  
  <p>I have a question : </p>
  
  <ul>
  <li>How to i get data from input user and compare with data from
  database. If not same, excute command random. It same value from
  database, it must return and create new random .Please help me
  because i working in 1 day and not to handle problem.</li>
  </ul>