plpgsql question/problem

["Hyatt, Gordon" <Gordon.Hyatt@xxxxxxxxxxxxxxxxxx>]

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 correct list, 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 new values.

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend