pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
plpgsql question/problem
5+ messages / 3 participants
[nested] [flat]

* plpgsql question/problem
@ 2007-08-13 20:24 Hyatt, Gordon <Gordon.Hyatt@joslin.harvard.edu>
  2007-08-13 20:46 ` Re: plpgsql question/problem Scott Marlowe <scott.marlowe@gmail.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Hyatt, Gordon @ 2007-08-13 20:24 UTC (permalink / raw)
  To: pgsql-admin


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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: plpgsql question/problem
  2007-08-13 20:24 plpgsql question/problem Hyatt, Gordon <Gordon.Hyatt@joslin.harvard.edu>
@ 2007-08-13 20:46 ` Scott Marlowe <scott.marlowe@gmail.com>
  2007-08-13 21:06   ` Re: plpgsql question/problem Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 5+ messages in thread

From: Scott Marlowe @ 2007-08-13 20:46 UTC (permalink / raw)
  To: Hyatt, Gordon <Gordon.Hyatt@joslin.harvard.edu>; +Cc: pgsql-admin

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 correct list, 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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: plpgsql question/problem
  2007-08-13 20:24 plpgsql question/problem Hyatt, Gordon <Gordon.Hyatt@joslin.harvard.edu>
  2007-08-13 20:46 ` Re: plpgsql question/problem Scott Marlowe <scott.marlowe@gmail.com>
@ 2007-08-13 21:06   ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 5+ messages in thread

From: Tom Lane @ 2007-08-13 21:06 UTC (permalink / raw)
  To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Hyatt, Gordon <Gordon.Hyatt@joslin.harvard.edu>; pgsql-admin

"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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: plpgsql question/problem
@ 2007-08-14 13:18 Hyatt, Gordon <Gordon.Hyatt@joslin.harvard.edu>
  2007-08-14 16:18 ` Re: plpgsql question/problem Scott Marlowe <scott.marlowe@gmail.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Hyatt, Gordon @ 2007-08-14 13:18 UTC (permalink / raw)
  To: pgsql-admin

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 all 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?

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 correct list, 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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: plpgsql question/problem
  2007-08-14 13:18 Re: plpgsql question/problem Hyatt, Gordon <Gordon.Hyatt@joslin.harvard.edu>
@ 2007-08-14 16:18 ` Scott Marlowe <scott.marlowe@gmail.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Scott Marlowe @ 2007-08-14 16:18 UTC (permalink / raw)
  To: Hyatt, Gordon <Gordon.Hyatt@joslin.harvard.edu>; +Cc: pgsql-admin

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 spent all 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.



^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2007-08-14 16:18 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-08-13 20:24 plpgsql question/problem Hyatt, Gordon <Gordon.Hyatt@joslin.harvard.edu>
2007-08-13 20:46 ` Scott Marlowe <scott.marlowe@gmail.com>
2007-08-13 21:06   ` Tom Lane <tgl@sss.pgh.pa.us>
2007-08-14 13:18 Re: plpgsql question/problem Hyatt, Gordon <Gordon.Hyatt@joslin.harvard.edu>
2007-08-14 16:18 ` Scott Marlowe <scott.marlowe@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox