Trigger that search inserted values in another table-CodePudding

I need to upload info from external databases into mine periodically. These are my tables

CREATE TABLE A
(
    id character varying primary key,
    name character varying UNIQUE
)

CREATE TABLE B
(
    id character varying primary key,
    name character varying 
)

CREATE TABLE C
(
    id character varying REFERENCES B(id),
    name character varying REFERENCES A(name)
)

To add some insight, A.name are known designations and B.name are texts that may contain or not those known designations. The matches are saved in table c. I do this work in R when uploading table b but I need a trigger that insert the new matches in C whenever a row is inserted in A (updates and deletes are solved with a cascade).

I am thinking in something like this (pseudocode):

CREATE TRIGGER trigger_name
   AFTER INSERT
   ON A FOR EACH ROW
AS
BEGIN
SET NOCOUNT ON;
 -- Loop throug all INSERTED.name
 -- Search each INSERTED.name value in B.name with: like '%INSERTED.name%'
 -- When found, insert in C both id and INSERTED.name 
END;

My biggest problem is that I can't figure out how to make the iterations properly in PLPGSQL or if this is a correct aproximation. Will it be better to just call the R script that loads the needed tables and map them again instead of using the inserted data?

Thanks and sorry if my question is not clear enough!

CodePudding user response：

Example how to write trigger function and how create triggers for tables:

CREATE FUNCTION a_after_insert() 
   RETURNS TRIGGER 
   LANGUAGE PLPGSQL
AS $$
declare 
    b_id varchar;
begin
    select 
        id into b_id 
    from b 
    where 
        b."name" like '%' || new."name" || '%';
    
    -- if record not found then b_id will be null;
    if (b_id is not null) then 
        insert into c (id, "name") values (b_id, new."name");   
    end if; 
    return new;
END;
$$

create trigger a_after_insert_trigger
after insert 
  on a
  for each row 
  execute procedure a_after_insert();

For each row in PostgreSQL - this means that even if 10 records are inserted into the table at once, the trigger works once for each row, so there is no need for a loop.

Only here there may be a problem, since we are searching like in b table, its result may be not one but several rows. You can also write it like this if you want:

CREATE FUNCTION a_after_insert() 
   RETURNS TRIGGER 
   LANGUAGE PLPGSQL
AS $$
begin
    
    insert into c (id, "name")
    select b.id, new.name   
    from b 
    where 
        b."name" like '%' || new."name" || '%';
    
    return new;
END;
$$