My question is the following:
I have a PostgreSQL database with several tables, what I want to do is the following, I will give you three tables as an example.
The first table is called teachers that has attributes name, id and type where type can only be "IN" or "EX"
I have a second table called internal teachers with the ID of all teachers with the attribute type = "IN" and another table called external teachers with the ID attribute of all teachers where type = "EX"
The thing is that at the moment I have to manually add the ID to the internal teachers and external teachers table, I would like to know if there is any way to that when entering the teacher's ID and its type to the teachers table, the database automatically places its ID in one table or another depending on its type.
Without more to say a greeting and thanks!
If you want automatically insertion in internal and external tables, you can use trigger as below:
Trigger Function
create or replace function trig_fun()
returns trigger AS
$$
begin
if(new.type='IN') then
insert into internal values(new.id);
end if;
if(new.type='EX') then
insert into external values(new.id);
end if;
return new;
end;
$$
language plpgsql
and attach it after insert event of teacher table
create trigger trig_on_insert
after insert on
teachers
for each row
execute procedure trig_fun()
You could implement your ‘internal’ and ‘external’ tables as separate views of the teachers table.
create table teachers as
select * from (values ( 1, 'EX')) as z(id, type);
create view external as (select * from teachers where type = 'EX');
create view internal as (select * from teachers where type = 'IN');
The following query:
select * from external;
gives:
(1, 'EX')
Whereas selecting from 'internal' gives an empty result (because there are no internal entries).
There is no Postgres functionality that automatically creates row in a child table on insert on the parent table. Foreign key constraints enforce integrity when the data changes and come with various options, but not the one you describe.
One option is to write trigger. You could also be explicit in the query, using Postgres’ returning syntax:
with
t as (
insert into teachers(name, type)
values (?, ?)
returning *
),
x as (
insert into teachers_internal(id)
select id from t where type = 'IN'
)
insert into teachers_external(id)
select id from t where type = 'EX'
I would recommend creating foreign keys too, so the proper action is taken when rows are dropped from the parent table (you probably want to delete the children row too).