CODEWITHSUNDEEP
sqlpostgresqltriggersplpgsqldatabase-trigger
Squall1
Squall1

Reputation: 11

How to compare the value the user enters with the data in the database when performing some random use of plpgsql

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:

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

My table structure is as follows: 

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

For convenience, I implement writing functions and triggers with plpgsql as follows:

//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);

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 :

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();

But when i excute command Insert

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

It's not working. PostgresSQL throw me exception :

QUERY: SELECT location FROM Student WHERE location = NEW.location CONTEXT: PL/pgSQL function student_no() line 8 at SQL statement SQL state: 42P01.

I have a question :

Upvotes: 1

Views: 57

Answers (1)

Pavel Stehule
Pavel Stehule

Reputation: 45805

There are more than one problem

  1. SELECT location FROM Student WHERE location = NEW.location; - PLpgSQL doesn't allow execute a query without some target for result. For SELECT a INTO clause is required. If you don't need to store result, use a PERFORM statement or better (in this case), use a predicate EXISTS So instead:

    -- 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. but this technique is not enough to protect you against race condition. In any time the database can be used by more users. Newer you see current last data. Any time you can see just some snapshot - and without locking or UNIQUE index the query like IF EXISTS(some query) THEN is not good protection against duplicates rows. It is not possible do it well without more aggressive locking from triggers. Your example is good example how to don't use triggers. Use this logic in explicitly called function (for example in plpgsql too), but not in trigger. For this case it is bad place.

  3. PLpgSQL is case insensitive language - don't use camel notation. SQL is case insensitive language - don't use camel notation and dont't use case sensitive SQL identifiers like "lastName" - it is the most short way to mental hospital.

Upvotes: 1

Related Questions