CODEWITHSUNDEEP
postgresql
iam_anirban
iam_anirban

Reputation: 156

how to insert record using view having joined tables postgresql?

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'));

Upvotes: 0

Views: 171

Answers (1)

Fabrice Chapuis
Fabrice Chapuis

Reputation: 498

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');

Upvotes: 1

Related Questions