Re: Issues with Information_schema.views

On Sat, Oct 28, 2023 at 5:27 PM Erki Eessaar <erki.eessaar@taltech.ee> 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.