I've created a view from the join of two tables called students and depts; Now I want to insert a record through the view in the students table. Simple INSERT INTO showing error -

ERROR: cannot insert into view "mec_engg", DETAIL: Views that do not select from a single table or view are not automatically updatable

Code for creating the view -

CREATE VIEW mec_engg AS
SELECT rollno, st_name, deptname,
AGE(current_date, bdate) AS Age
FROM students, depts
WHERE students.deptcode=depts.deptcode
AND depts.deptname='Mechanical Engineering';

Depts table -

CREATE TABLE depts(
    deptcode CHAR(3) PRIMARY KEY,
    deptname CHAR(30) NOT NULL
);

Students Table -

CREATE TABLE students(
    rollno SERIAL PRIMARY KEY,
    st_name CHAR(30),
    bdate DATE CHECK(bdate>'01-JAN-97'),
    deptcode CHAR(3) REFERENCES depts(deptcode) ON DELETE CASCADE,
    hostel SMALLINT CHECK(hostel<10),
    parent_inc NUMERIC(8,1)
);

How can I insert the record through the view in students table?

INSERT INTO mec_engg(rollno, st_name, deptname, age)
VALUES (92005454, 'Abhishek Ganguly', 'Mechanical Engineering', AGE(current_date, DATE'22-Oct-2000'));

CodePudding user response：

One possibility is to use a trigger instead of as mentioned in the insert command output. Two more points are important:

1. age field does not exist in any of the students and depts tables therefore the age value cannot be in the insert command.

2. The deptcode field is a primary key and must be referenced in the insert command

   CREATE OR REPLACE FUNCTION mec_engg_insert_data()
    RETURNS trigger AS
    $$
    BEGIN
    INSERT INTO "students" ("rollno", "st_name") VALUES (NEW."rollno", 
    NEW."st_name");
    INSERT INTO "depts" (deptcode, deptname) VALUES (1, NEW."deptname");
    RETURN NEW;
    END;
    $$
    LANGUAGE 'plpgsql';
   
    CREATE TRIGGER mec_engg_insert
    INSTEAD OF INSERT ON mec_engg
    FOR EACH ROW
    EXECUTE PROCEDURE mec_engg_insert_data();
   
    INSERT INTO mec_engg(rollno, st_name, deptname)
    VALUES (92005454, 'Abhishek Ganguly', 'Mechanical Engineering');