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

Remember to vote!