Hi all,
I have a question regarding rules on views. Are the commands inside a ON
INSERT (or UPDATE) DO INSTEAD (implicit) transactions for postgres? I
tried to put BEGIN; and COMMIT; around the commands in a ON ... DO
INSTEAD ( ) block, but keep getting a syntax error.
The following example might explain what I'm trying to find out:
-------------------------------------------------------------
Two tables, "a" and "b". "b" is referencing "a" via "a_id"...
CREATE TABLE a
(
id serial,
foo varchar(255),
CONSTRAINT aid PRIMARY KEY (id)
);
CREATE TABLE b
(
id serial,
a_id int4 not null,
foo varchar(255),
CONSTRAINT bid PRIMARY KEY (id),
CONSTRAINT bfk FOREIGN KEY (a_id)
REFERENCES a (id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
);
...a view "ab" for a combination of the before mentioned tables...
CREATE OR REPLACE VIEW ab AS
SELECT a.id AS main_id, a.foo AS from_a, b.foo AS from_b
FROM a, b
WHERE a.id = b.a_id;
...and a rule "ab_insert"...
CREATE OR REPLACE RULE ab_insert AS ON INSERT TO ab DO INSTEAD (
INSERT INTO a (foo) VALUES (new.from_a);
INSERT INTO b (foo,a_id) VALUES (new.from_b,currval('a_id_seq'));
);
-------------------------------------------------------------
As you can see, the "ab_insert" rule inserts into "a" first and than
takes the current value of "a.id"'s sequence to set the reference "a_id"
in "b".
Can I assume that this will always work as expected or is it possible
that in a multi-user scenario two or more concurrent inserts on the view
will lead to undesirable results?
As mentioned in the beginning, putting BEGIN; and COMMIT; didn't work.
Is this kind of creating a relation between two or more tables and
relying on a sequence generally a good practice?
Regards,
Oliver