Hello The following was tested in a PostgreSQL (16) database. In my opinion queries based on Information_schema.views sometimes give unexpected results.
CREATE TABLE Dept(deptno SMALLINT NOT NULL, dname VARCHAR(50) NOT NULL, CONSTRAINT pk_dept PRIMARY KEY (deptno)); CREATE TABLE Emp(empno INTEGER NOT NULL, ename VARCHAR(50) NOT NULL, deptno SMALLINT NOT NULL, CONSTRAINT pk_emp PRIMARY KEY (empno), CONSTRAINT fk_emp_dept FOREIGN KEY (deptno) REFERENCES Dept(deptno) ON UPDATE CASCADE); CREATE VIEW emps AS SELECT * FROM Dept INNER JOIN Emp USING (deptno); UPDATE Emps SET ename=Upper(ename); /*ERROR: cannot update view "emps" DETAIL: Views that do not select from a single table or view are not automatically updatable. HINT: To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON UPDATE DO INSTEAD rule.*/ SELECT table_schema AS schema, table_name AS view, is_updatable, is_insertable_into FROM Information_schema.views WHERE table_name='emps'; /*is_updatable=NO and is_insertable_into=NO*/ CREATE OR REPLACE RULE emps_insert AS ON INSERT TO Emps DO INSTEAD NOTHING; /*After that: is_insertable_into=YES*/ CREATE OR REPLACE RULE emps_update AS ON UPDATE TO Emps DO INSTEAD NOTHING; /*After that: is_updatable=NO*/ CREATE OR REPLACE RULE emps_delete AS ON DELETE TO Emps DO INSTEAD NOTHING; /*After that: is_updatable=YES*/ 1. Indeed, now I can execute INSERT/UPDATE/DELETE against the view without getting an error. However, I still cannot change the data in the database through the views. 2. is_updatable=YES only after I add both UPDATE and DELETE DO INSTEAD NOTHING rules. My question is: are 1 and 2 the expected behaviour or is there a mistake in the implementation of the information_schema view? Best regards Erki Eessaar
