Обсуждение: insert into a view?
Tried it but didn't work. It gave me a hint though to try triggers. Can anyone show me how to do an insert into a view using triggers? Thanks. :-)
On Mon, May 01, 2006 at 05:23:22PM -0700, Karen Hill wrote: > Tried it but didn't work. It gave me a hint though to try triggers. > Can anyone show me how to do an insert into a view using triggers? Rules are probably what you're after; the documentation has examples. http://www.postgresql.org/docs/8.1/interactive/rules.html http://www.postgresql.org/docs/8.1/interactive/rules-update.html#RULES-UPDATE-VIEWS -- Michael Fuhr
"Karen Hill" <karen_hill22@yahoo.com> writes: > Tried it but didn't work. It gave me a hint though to try triggers. > Can anyone show me how to do an insert into a view using triggers? 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) regards, tom lane
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.*);
On Wed, May 03, 2006 at 10:02:17AM -0700, Karen Hill wrote: > > Tom Lane wrote: > > 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; Not related directly to your issue, but you may find that explicit JOINs help are easier to debug and maintain, as in: CREATE VIEW v AS SELECT t1.*, t2.foo, t2.bar FROM t1 JOIN t2 ON (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.*); More like this: CREATE RULE r AS ON INSERT INTO v DO INSTEAD ( INSERT INTO t1 VALUES (NEW.num, NEW.baz, NEW.blur, NEW.quux); INSERT INTO t2 VALUES (NEW.num, NEW.foo, NEW.bar); ); HTH :) Cheers, D -- David Fetter <david@fetter.org> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter Remember to vote!