WB51fj5E

1create table employee
2(
3   empID SERIAL unique PRIMARY KEY NOT NULL,
4   first_name text not null,
5   mi text not null,
6   last_name text not null,
7   full_name character varying (150) UNIQUE NOT NULL,
8   employee_email character varying(150) UNIQUE
9   );
10
11   CREATE TABLE empmerge
12(
13  empid integer NOT NULL REFERENCES employee(empID) MATCH FULL
14      ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE,
15  full_name character varying NOT NULL REFERENCES employee(full_name) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE,
16  org_section text,
17  date_of_xfer date
18
19);
20
21create or replace function concat_full_name() returns trigger as $$
22begin
23    if NEW.full_name is null then NEW.full_name := concat(new.first_name, ' ',new.mi,'.', ' ', new.last_name);
24    end if;
25    return new;
26end;
27$$ language plpgsql;
28
29create trigger trig_concat_full_name
30before insert
31on employee
32for each row
33execute procedure concat_full_name();
34
35create or replace function insert_empMerge() returns trigger as $$
36begin
37INSERT INTO empMerge(empID, full_name) 
38SELECT employee.empID, employee.full_name
39FROM employee
40WHERE NOT EXISTS (SELECT 1
41              FROM empMerge
42              WHERE employee.empID = empMerge.empID and  employee.full_name = empMerge.full_name );
43    return new;
44end;
45$$ language plpgsql;
46
47create trigger trig_insert_empMerge
48before insert
49on employee
50for each row
51execute procedure insert_empMerge();
52
53insert into employee(first_name, mi, last_name) values ('dora','l', 'explorer'), ('atticus','m', 'finch'), ('nancy','s', 'drew');
54
55select * from empMerge;