Обсуждение: plpgsql question/problem

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

On 8/13/07, Hyatt, Gordon <Gordon.Hyatt@joslin.harvard.edu> wrote:
>
> 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.) 

CODE SNIPPED.

> When I attempt to run insert_a_and_b, insert_b fails with a foreign key violation.

I ran your exact same code and it worked fine.  Note that I changed one line:

begin;
insert_a_and_b ('some text for a', 'some text for b');
commit;

became:

begin;
select insert_a_and_b ('some text for a', 'some text for b');
commit;

But I assume that's just a simple typo. I'm running pgsql 8.1.8

"Scott Marlowe" <scott.marlowe@gmail.com> writes:
> On 8/13/07, Hyatt, Gordon <Gordon.Hyatt@joslin.harvard.edu> wrote:
>> When I attempt to run insert_a_and_b, insert_b fails with a foreign key violation.

> I ran your exact same code and it worked fine.  Note that I changed one line:

Works for me too (with Scott's correction), in 8.1.x, 8.2.x, and HEAD.

            regards, tom lane

Scott and Tom,

Thanks for your replies.  Yes, the missing select keyword was simply a typo on my part.

The code that I sent was simplified (I didn't include all the fields, most of which are character varying).  I've spent
allday yesterday trying to make it work.  (I'm not sure if I mentioned this, but I using 8.2.4 on Win2000 SP4). 

What else could be causing this code to correctly return the next sequence number but not insert anything into the
table?

Thanks in advance,

Gord


-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
Sent: Monday, August 13, 2007 4:46 PM
To: Hyatt, Gordon
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] plpgsql question/problem


On 8/13/07, Hyatt, Gordon <Gordon.Hyatt@joslin.harvard.edu> wrote:
>
> 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.) 

CODE SNIPPED.

> When I attempt to run insert_a_and_b, insert_b fails with a foreign key violation.

I ran your exact same code and it worked fine.  Note that I changed one line:

begin;
insert_a_and_b ('some text for a', 'some text for b');
commit;

became:

begin;
select insert_a_and_b ('some text for a', 'some text for b');
commit;

But I assume that's just a simple typo. I'm running pgsql 8.1.8

On 8/14/07, Hyatt, Gordon <Gordon.Hyatt@joslin.harvard.edu> wrote:
> Scott and Tom,
>
> Thanks for your replies.  Yes, the missing select keyword was simply a typo on my part.
>
> The code that I sent was simplified (I didn't include all the fields, most of which are character varying).  I've
spentall day yesterday trying to make it work.  (I'm not sure if I mentioned this, but I using 8.2.4 on Win2000 SP4). 
>
> What else could be causing this code to correctly return the next sequence number but not insert anything into the
table?

Can you post a proven failure case?  One that you can put into a nice
freshly created db and it will fail?  Without that, I can't think of
much we can do to help.