I am currently working under postgres 14. I got two tables customers and contacts, and i want to automatically find the customer_id from the customer name when i add a new contact in the table contacts.

CREATE TABLE customers(
   customer_id INT GENERATED ALWAYS AS IDENTITY,
   customer_name VARCHAR(255) NOT NULL UNIQUE,
   PRIMARY KEY(customer_id)
);

CREATE TABLE contacts(
   contact_id INT GENERATED ALWAYS AS IDENTITY,
   customer_id INT,
   contact_name VARCHAR(255) NOT NULL UNIQUE,
   PRIMARY KEY(contact_id),
   CONSTRAINT fk_customer
      FOREIGN KEY(customer_id) 
      REFERENCES customers(customer_id)
);

INSERT INTO customers(customer_name) VALUES('my_first_customer')

So from here i want to do something like that :

INSERT INTO contacts(customer_id, contact_name ) VALUES('my_first_customer',"thomas")

and i want to get this result in contacts table :

i tried to make a function to change the value from name to id but get an error of type. Because the the type error is catch before the trigger. Here is my function

CREATE FUNCTION get_id_fct() 
RETURNS TRIGGER AS $get_id_fct$
DECLARE val_id INTEGER;
BEGIN 
SELECT customer_id INTO val_id FROM customers WHERE customers.customer_id = NEW.customer_id;
NEW.customer_id := val_id; 
RETURN NEW
END
$get_id_fct$  LANGUAGE plpgsql;

CREATE TRIGGER get_id
BEFORE INSERT ON contacts
FOR EACH ROW EXECUTE PROCEDURE get_id_fct();'

Is their a way around that ? or specific method to do this task that i don't know about ? I am quite a beginner at SQL.

CodePudding user response：

You can make a subquery in insert statement

INSERT INTO contacts(customer_id, contact_name ) VALUES (
(select customer_id from customers where customer_name = 'my_first_customer') ,'thomas');

This query will fail if there is more than one customer with given name, and insert a null value for customer_id if there is no customers with given name

Different approach (proposed by @wildplasser)

INSERT INTO contacts(customer_id, contact_name ) 
select customer_id,'thomas' from customers where customer_name = 'my_first_customer';

In this case, when there is no customer with given name, no row will be created. When there is more than one customer with given name, for each of them record will be created.

Or you can create view with INSTEAD OF trigger.

create view v_contacts as 
select customer_name, contact_name from customers 
  join contacts on customers.customer_id = contacts.customer_id;

CREATE FUNCTION emp () RETURNS trigger AS $$
BEGIN
    
  INSERT INTO contacts(customer_id, contact_name ) 
    VALUES((select customer_id from customers where customer_name = 
       NEW.customer_NAME),NEW.contact_NAME);
    
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER v_contactst
    INSTEAD OF INSERT ON v_contacts
    FOR EACH ROW
EXECUTE FUNCTION emp();    

Insert statement

INSERT INTO v_contacts(customer_name, contact_name ) VALUES('my_first_customer','thomas')

And just a note, that columns customer_name and contact_name are not unique so this code is not safe and throw error if customer_name didn't exists or there is more than one record with this customer_name