Postgresql Simultaneous Insert into Parent/Child Table-CodePudding

Using Postgresql, I have tables with a parent/child or a general/detail relationship, a bit like Object-Oriented inheritance:

CREATE TYPE person_kinds AS ENUM ('student', 'teacher');

CREATE TABLE person(id serial primary key, kind person_kinds, name text);
CREATE TABLE teacher(person_id integer primary key references person(id), subject text);
CREATE TABLE student(person_id integer primary key references person(id), grade integer);

That is, the person table contains all the things that are common to a person and keeps a type tag. The subtables refine the information in person by adding additional columns depending on the type; e.g. a teacher teaches a subject, a student may have a grade. This table structure is given and I cannot change it. (e.g. I don't think I could add inheritance, but I am not convinced this would help anyways)

Now I have a temp table with student data that is a "flat" version of the above. Eg

CREATE TABLE tmp_table(kind person_kinds, name text, grade integer);
INSERT INTO tmp_table(kind, name, grade) 
VALUES ('student', 'Chris', 2),('student','Lara',1),('student','Izzy',3);

My question is, how to insert the data from the temp table into the above tables? I have difficulties because the insert for the student table needs the person-id as the foreign-key and because the student-table doesn't have any "distinguishing" column so I cannot join anything.

I tried things along the following lines:

with inserted_persons as
( 
  insert into person(type, name) 
  select type, name 
  from tmp_table 
  returning id  --I sadly cannot return any columns from tmp_table here
)
insert into student(person_id, grade) 
select 
  p.id, t.grade 
from 
  -- I don't have a column to join on, and the implicit join here 
  -- is wrong as it crosses the tmp_table with the inserted_persons
  inserted_persons as p, 
  tmp_table as t

CodePudding user response：

I would solve that by creating a view for students:

CREATE VIEW v_student AS
SELECT person.id, person.name, student.grade
FROM person
   JOIN student
      ON person.id = student.person_id
WHERE person.kind = 'student';

and an INSTEAD OF INSERT trigger:

CREATE FUNCTION ins_student() RETURNS trigger
   LANGUAGE plpgsql AS
$$DECLARE
   v_id integer;
BEGIN
   INSERT INTO person (kind, name)
      VALUES ('student', NEW.name)
      RETURNING id INTO v_id;

   INSERT INTO student (person_id, grade)
      VALUES (v_id, NEW.grade);

   RETURN NEW;
END;$$;

CREATE TRIGGER ins_student INSTEAD OF INSERT ON v_student
   FOR EACH ROW EXECUTE FUNCTION ins_student();

That can be used like this:

INSERT INTO v_student (name, grade)
SELECT name, grade FROM tmp_table
WHERE kind = 'student';

TABLE person;

 id │  kind   │ name  
════╪═════════╪═══════
  1 │ student │ Chris
  2 │ student │ Lara
  3 │ student │ Izzy
(3 rows)

TABLE student;

 person_id │ grade 
═══════════╪═══════
         1 │     2
         2 │     1
         3 │     3
(3 rows)