Re: insert into a view?

From: "Karen Hill" <karen_hill22(at)yahoo(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: insert into a view?
Date: 2006-05-03 17:02:17
Message-ID: 1146675737.225401.245260@v46g2000cwv.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


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.*);

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Joao Miguel Ferreira 2006-05-03 17:15:37 Re: database size grows (even after vacuum (full and analyze))....
Previous Message Sven Willenberger 2006-05-03 16:53:31 Re: out of memory for query result