Re: insert into a view?

["Karen Hill" <karen_hill22@xxxxxxxxx>]

Tom Lane wrote:

> I hope it said rules, because you can't put a trigger on a view.
>
> regression=# create table t(f1 int, f2 text);
> CREATE TABLE
> regression=# create view v as select * from t;
> CREATE VIEW
> regression=# insert into v values(22, 'foo');
> ERROR:  cannot insert into a view
> HINT:  You need an unconditional ON INSERT DO INSTEAD rule.
> regression=# create rule r as on insert to v do instead
> regression-# insert into t values(new.*);
> CREATE RULE
> regression=# insert into v values(22, 'foo');
> INSERT 0 1
> regression=# select * from t;
>  f1 | f2
> ----+-----
>  22 | foo
> (1 row)

Thanks Tom,

I tried it and it worked.  Is it possible to do something a bit more
complex?  Can you use rules to insert into a view that has multiple
tables as the source?  For example:

CREATE VIEW v AS SELECT * FROM t1, t2 WHERE t1.num = t2.num;

Would the rule for the above look something like this?

CREATE RULE r AS ON INSERT INTO t1, t2 WHERE t1.num = t2.num DO INSTEAD
INSERT INTO t1 , t2 VALUES (new.*);