I'm not sure if this is the correct list for this question, but I'll give it a shot anyway. (If this is not the
correctlist, please suggest the correct one.)
I've written a number of plpgsql functions to convert a database from MS Access into Postgres (v8.2.4 on Windows 2000
SP4)and have the following problem:
data to table fields having a foreign key reference cannot be added via insert...values.
For example table_a's schema:
<code>
create table table_a
(
id serial,
name character varying,
constraint pk_table_a primary key (id)
)
without oids;
create table table_b
(
id serial,
name character varying,
a_id int4,
constraint pk_table_b primary key (id),
constraint fk_table_a_table_b foreign key (a_id)
references table_a(id) match full
on update restrict on delete restrict
)
without oids;
create or replace function insert_a (a_name text) returns integer as
$$
declare
a_id integer;
begin
a_id := nextval('table_a_id_seq');
insert into table_a (id, name) values (a_id, a_name);
return a_id;
end;
$$ language plpgsql;
create or replace function insert_b (b_name text, p_id integer) returns integer as
$$
declare
b_id integer;
begin
b_id := nextval ('table_b_id_seq');
insert into table_b (id, name, a_id) values (b_id, b_name, p_id);
return b_id;
end;
$$ language plpgsql;
create or replace function insert_a_and_b (a_name text, b_name text) returns integer as
$$
declare
a_id integer;
begin
a_id := insert_a (a_name);
return insert_b (b_name, a_id);
end;
$$ language plpgsql;
begin;
insert_a_and_b ('some text for a', 'some text for b');
commit;
</code>
When I attempt to run insert_a_and_b, insert_b fails with a foreign key violation.
If I change the insert...values in insert_b to:
insert into table_b (id, name, a_id) select b_id, b_name, A.id from table_a A where A.id=p_id;
then the function runs without complaint, but nothing is inserted into either table. The sequences are updated to the
newvalues.
I've also tried changing the assignments to select ... into statements with no difference.
I've done similar things in 8.1.x without issue.
Something just doesn't add up - I can't see what is the problem.
Can anyone help me?
Thanks,
Gord