Hello
Thank you! I know that.
DO INSTEAD NOTHING rules on updatable views could be used as a way to implement
WITH READ ONLY constraint (one can define such constraint in Oracle). However,
one could accidentally add such rule to non-updatable view as well.
I tried to construct a system-catalog based query to find database rules that
are unnecessary. Thus, for the testing purposes I added a DO INSTEAD NOTHING
rule to already non-updatable view and was a bit surprised that
INFORMATION_SCHEMA-based check showed that the view had become updatable. A
possible reasoning is that I can update the view without getting an error.
However, I still cannot change data in base tables.
Secondly, the rule you demonstrated does not alone change IS_UPDATABLE value to
YES. I have to create two rules:
CREATE OR REPLACE RULE emps_update AS ON UPDATE
TO Emps
DO INSTEAD UPDATE emp SET
empno = NEW.empno,
ename = NEW.ename,
deptno = NEW.deptno;
CREATE OR REPLACE RULE emps_delete AS ON DELETE
TO Emps
DO INSTEAD DELETE FROM Emp WHERE empno=OLD.empno;
My question is - is all of this the intended behaviour by the implementers?
Best regards
Erki Eessaar
________________________________
From: jian he <[email protected]>
Sent: Saturday, October 28, 2023 13:38
To: Erki Eessaar <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: Issues with Information_schema.views
On Sat, Oct 28, 2023 at 5:27 PM Erki Eessaar <[email protected]> wrote:
>
> Hello
>
>
> /*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.
https://www.postgresql.org/docs/current/sql-createview.html
"
A more complex view that does not satisfy all these conditions is
read-only by default: the system will not allow an insert, update, or
delete on the view. You can get the effect of an updatable view by
creating INSTEAD OF triggers on the view, which must convert attempted
inserts, etc. on the view into appropriate actions on other tables.
For more information see CREATE TRIGGER. Another possibility is to
create rules (see CREATE RULE), but in practice triggers are easier to
understand and use correctly.
"
You CAN get the effect of an updateable view. But you need to make the
rule/triggers correct.
the following RULE can get the expected result.
CREATE OR REPLACE RULE emps_update AS ON UPDATE
TO Emps
DO INSTEAD UPDATE emp SET
empno = NEW.empno,
ename = NEW.ename,
deptno = NEW.deptno;
you can also look at src/test/regress/sql/triggers.sql,
src/test/regress/sql/rules.sql for more test cases.
