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

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

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

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related