pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedplpgsql question/problem
5+ messages / 3 participants
[nested] [flat]
* plpgsql question/problem
@ 2007-08-13 20:24 Hyatt, Gordon <Gordon.Hyatt@joslin.harvard.edu>
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:46 Scott Marlowe <scott.marlowe@gmail.com>
parent: Hyatt, Gordon <Gordon.Hyatt@joslin.harvard.edu>
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 21:06 Tom Lane <tgl@sss.pgh.pa.us>
parent: Scott Marlowe <scott.marlowe@gmail.com>
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>
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 16:18 Scott Marlowe <scott.marlowe@gmail.com>
parent: Hyatt, Gordon <Gordon.Hyatt@joslin.harvard.edu>
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