Create a trigger function in Postgres that doesn't let you have two entries with the same id

Question

I have two tables in postgres, I want to create a function that doesn’t have more than 2 loans in the lending table with the same person id. example: in the loan table I cannot have 3 loans that are from the same person, that is, we loan with the same person's id.

I need to do this using a function, I put what I was trying to do but it didn't work

CREATE TABLE person (
    name_person varchar (100) ,
    id_person varchar(14) primary key
)

CREATE TABLE lending(
    id_lending primary key (100) ,
    id_publication (14) FK,
    id_person fk REFERENCES id_person (person)




CREATE OR REPLACE FUNCTION check_numlending() 
RETURNS trigger AS 
$BODY$ 
BEGIN 
IF( select * from lending
   inner join person 
   on person.id_person = lending.id_person > 2  ) THEN 
    RAISE EXCEPTION 'ERROR'; 
END IF; 
RETURN NEW; 
END; 
$BODY$ 
LANGUAGE plpgsql; 


-- Trigger
CREATE TRIGGER  
trg_check_num_lending  
BEFORE INSERT OR UPDATE ON 
lendingFOR EACH ROW EXECUTE PROCEDURE check_numlending();

What is NEW.id_person = person.id_person > 2 supposed to do? If you want to verify some condition on the existing table data, you need to execute a SELECT inside the function — crizzis
– crizzis, Commented Jun 6, 2020 at 17:59
hello, I fixed this part, what I want is almost that ... but how do I check if there is more than twice the id_person in the lending table? — ramos97
– ramos97, Commented Jun 6, 2020 at 18:03
Well, if you want to learn how many rows there are in a query result, you typically use SELECT COUNT(*)... You would be well advised to learn the basics of SQL first; otherwise, you're going to have a hard time writing DB procedures and functions — crizzis
– crizzis, Commented Jun 6, 2020 at 18:08

Akhilesh Mishra · Accepted Answer · 2020-06-06 18:06:36Z

1

Write your trigger Function like below:

-- Function
CREATE OR REPLACE FUNCTION check_numlending() 
RETURNS trigger AS 
$BODY$ 
declare counter int;
BEGIN 
select count(*) into counter from lending where id_person =new.id_person;
IF( counter>=2  ) THEN 
    RAISE EXCEPTION 'ERROR'; 
END IF; 
RETURN NEW; 
END; 
$BODY$ 
LANGUAGE plpgsql; 


-- Trigger
CREATE TRIGGER  
trg_check_num_lending  
BEFORE INSERT OR UPDATE ON 
lending FOR EACH ROW EXECUTE PROCEDURE check_numlending();

answered Jun 6, 2020 at 18:06

Akhilesh Mishra

6,1503 gold badges20 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create a trigger function in Postgres that doesn't let you have two entries with the same id

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related