From: Peter Atkins <peter.atkins@NXCD.com>
To: Ken Corey <ken.corey@atomic-interactive.com>
Cc: 'pgsql-sql@postgresql.org' <pgsql-sql@postgresql.org>
Subject: Re: Returning PK of first insert for second insert use.
Date: Mon, 29 Jul 2002 12:52:01 -0700
Message-ID: <1CAD483B723BD611B0C10090274FF0685547CB@NXCDMAIL> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: peter.atkins@NXCD.com, ken.corey@atomic-interactive.com
Subject: Re: Returning PK of first insert for second insert use.
In-Reply-To: <1CAD483B723BD611B0C10090274FF0685547CB@NXCDMAIL>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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