On Fri, Jun 18, 2004 at 16:13:38 +0300,
"raptor@tvskat.net" <raptor@tvskat.net> wrote:
> hi,
>
> I want to make the following thing :
> select-based updatable VIEW, which have two more virtual-fields.
> One of them is concatenation of others and the second is calculated on the fly.
> Can I do this and if yes how? can u give some example?
You can do this using the rule system.
Below is a dump of a test of an updatable view definition that I made with
playing with this. I don't have the original source script. The pg_dump
output is a bit verbose with constraint definitions, but it should be fine
for showing you how to make simple updatable views.
CREATE TABLE test1 (
id serial NOT NULL,
name text NOT NULL
);
CREATE TABLE test2 (
id serial NOT NULL,
name text NOT NULL
);
CREATE TABLE test3 (
id1 integer NOT NULL,
id2 integer NOT NULL
);
CREATE VIEW test4 AS
SELECT test1.name AS name1, test2.name AS name2 FROM test1, test2, test3 WHERE ((test1.id = test3.id1) AND
(test2.id= test3.id2));
ALTER TABLE ONLY test1
ADD CONSTRAINT test1_name_key UNIQUE (name);
ALTER TABLE ONLY test2
ADD CONSTRAINT test2_pkey PRIMARY KEY (id);
ALTER TABLE ONLY test2
ADD CONSTRAINT test2_name_key UNIQUE (name);
ALTER TABLE ONLY test3
ADD CONSTRAINT test3_pkey PRIMARY KEY (id1, id2);
ALTER TABLE ONLY test3
ADD CONSTRAINT "$1" FOREIGN KEY (id1) REFERENCES test1(id);
ALTER TABLE ONLY test3
ADD CONSTRAINT "$2" FOREIGN KEY (id2) REFERENCES test2(id);
CREATE RULE test4_ins AS ON INSERT TO test4 DO INSTEAD INSERT INTO test3 (id1, id2) SELECT test1.id, test2.id FROM
test1,test2 WHERE ((test1.name = new.name1) AND (test2.name = new.name2));
CREATE RULE test4_del AS ON DELETE TO test4 DO INSTEAD DELETE FROM test3 WHERE ((((test1.name = old.name1) AND
(test2.name= old.name2)) AND (test1.id = test3.id1)) AND (test2.id = test3.id2));
CREATE RULE test4_upd AS ON UPDATE TO test4 DO INSTEAD UPDATE test3 SET id1 = a1.id, id2 = a2.id FROM test1 a1, test2
a2,test1 b1, test2 b2 WHERE ((((((a1.name = new.name1) AND (a2.name = new.name2)) AND (test3.id1 = b1.id)) AND
(test3.id2= b2.id)) AND (b1.name = old.name1)) AND (b2.name = old.name2));
