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