Re: Returning PK of first insert for second insert use. - Mailing list 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.corey@atomic-interactive.com]
Sent: Monday, July 29, 2002 11:59 AM
To: Peter Atkins
Cc: 'pgsql-sql@postgresql.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