From: | Peter Atkins <peter(dot)atkins(at)NXCD(dot)com> |
---|---|
To: | 'Ken Corey' <ken(dot)corey(at)atomic-interactive(dot)com> |
Cc: | "'pgsql-sql(at)postgresql(dot)org'" <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Returning PK of first insert for second insert use. |
Date: | 2002-07-29 19:52:01 |
Message-ID: | 1CAD483B723BD611B0C10090274FF0685547CB@NXCDMAIL |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Ken,
Is there a possibility of another application accessing the DB and using the
id before my function has completed the transaction? I'm concerned with the
possibility of cross-over of ID's if the insert hangs.
There's no way to return the id of that insert inherently, and then use it
for the second insert? I think SQL uses something like ADD_ID, not sure.
Thanks,
-p
-----Original Message-----
From: Ken Corey [mailto:ken(dot)corey(at)atomic-interactive(dot)com]
Sent: Monday, July 29, 2002 11:59 AM
To: Peter Atkins
Cc: 'pgsql-sql(at)postgresql(dot)org'
Subject: Re: Returning PK of first insert for second insert use.
On Mon, 2002-07-29 at 19:32, Peter Atkins wrote:
> I have two tables t_proj, t_task see below:
>
> CREATE TABLE t_proj (
> proj_id SERIAL NOT NULL,
> PRIMARY KEY (proj_id),
> task_id integer(12),
> user_id integer(6),
> title varchar(35),
> description varchar(80)
> );
>
> CREATE TABLE t_task (
> task_id SERIAL NOT NULL,
> PRIMARY KEY (task_id),
> title varchar(35),
> description varchar(80)
> );
>
> When I insert into t_task I need to return the task_id (PK) for that
insert
> to be used for the insert into the t_proj table.
>
> I tried using RESULT_OID but I have no idea how to obtain the true PK
using
> this opague id. Below is the procedure I tried to use.
Since the primary key of the first table is a SERIAL, it's really
defined as something like this:
create table t_task (
task_id int4 not null default nextval('t_task_task_id_seq'),
...
Which means that you can predict what the next value will be, store that
in a temporary var, and then insert it into both tables...
CREATE OR REPLACE FUNCTION insertTask (varchar, varchar)
RETURNS INTEGER AS '
DECLARE
-- local variables
oid1 INTEGER;
retval INTEGER;
tempvar int4;
BEGIN
select into tempvar nextval(''t_task_task_id_seq'');
INSERT INTO t_task (task_id, title, description)
VALUES (tempvar,$1, $2);
-- Everything has passed, return id as pk
RETURN tempvar;
END;
' LANGUAGE 'plpgsql';
WARNING: this is not guaranteed to be the correct syntax, I didn't
create the tables and the function to test it, but I do this kind of
thing all the time in my functions.
--
Ken Corey CTO http://www.atomic-interactive.com 07720 440 731
From | Date | Subject | |
---|---|---|---|
Next Message | Gunther Schadow | 2002-07-29 20:00:16 | Decision support query inefficiencies ... |
Previous Message | Ken Corey | 2002-07-29 18:59:21 | Re: Returning PK of first insert for second insert use. |