mock5c
asked on
currval() when doing insert/select
I have created a view with a rule that allows me to insert into 2 tables that have a base/child 1:1 relationship. The base table has a projtag_id field and the child table has a FK to this field. I'm trying to use currval() to determine what the current projtag_id is after the row was inserted into the base table when using an INSERT...SELECT style query.
The problem is that it seems all records are inserted into the base table first. Then the child table will get currval() only after the last id entered into the base table. It does not do after every row insert.
For example, if my sequence starts at 100 and I insert 100 rows using INSERT...SELECT, then the child table will get 200 for the projtag_id for every row instead of 101,102,103,...
I've included my rule. This is for Postgres.
The problem is that it seems all records are inserted into the base table first. Then the child table will get currval() only after the last id entered into the base table. It does not do after every row insert.
For example, if my sequence starts at 100 and I insert 100 rows using INSERT...SELECT, then the child table will get 200 for the projtag_id for every row instead of 101,102,103,...
I've included my rule. This is for Postgres.
CREATE OR REPLACE RULE rule_base_test_i AS
ON INSERT TO base_test
DO INSTEAD (
INSERT INTO base_test_t
(project_id,projtag_name)
VALUES
(new.project_id,new.projtag_name);
INSERT INTO child_test_t (projtag_id,projtag_type,required_values)
VALUES ((select currval('base_test_t_projtag_id_seq')),new.projtag_type,new.required_values);
);
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window
Not tested