Re: virtual fields on VIEW?

On Fri, Jun 18, 2004 at 16:13:38 +0300,
"raptor(at)tvskat(dot)net" <raptor(at)tvskat(dot)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));