Re: insert into a view?

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(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!