Thread: Returning PK of first insert for second insert use.
All,
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.
CREATE OR REPLACE FUNCTION insertTask (varchar, varchar) RETURNS INTEGER AS '
DECLARE -- local variables oid1 INTEGER; retval INTEGER;
BEGIN INSERT INTO t_task (title, description) VALUES ($1, $2);
-- Get the oid of the row just inserted. GET DIAGNOSTICS oid1 = RESULT_OID;
retval := oid1;
-- Everything has passed, return id as pk RETURN retval; END;
' LANGUAGE 'plpgsql';
Any help would be great!
Thanks Again,
-p
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
Thank you for explaining that in detail it makes sense now. I'll give it a try. Thanks again! -p -----Original Message----- From: Ken Corey [mailto:ken.corey@atomic-interactive.com] Sent: Monday, July 29, 2002 1:05 PM 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 20:52, Peter Atkins wrote: > 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. That's the beauty of the nextval statement. The database internally sequences requests to it so that you're kept out of harm's way. Say process A called the function,and nextval returns 16. The function now continues on its way, but is not finished when process B then calls the function (before A is done), and nextval returns 17. So, then function called by process A returns 16, and the function called by process B returns 17. That means that unless the results of process B depend in some way upon the results of process A, there's no problem. -Ken -- Ken Corey CTO http://www.atomic-interactive.com 07720 440 731
You need to do something like this: SELECT proj_id FROM t_proj WHERE oid=xxx; To find value of primary key from oid. Chris > -----Original Message----- > From: pgsql-sql-owner@postgresql.org > [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Peter Atkins > Sent: Tuesday, 30 July 2002 2:33 AM > To: 'pgsql-sql@postgresql.org' > Subject: [SQL] Returning PK of first insert for second insert use. > > > All, > > 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. > > CREATE OR REPLACE FUNCTION insertTask (varchar, varchar) > RETURNS INTEGER AS ' > > DECLARE > -- local variables > oid1 INTEGER; > retval INTEGER; > > BEGIN > INSERT INTO t_task (title, description) VALUES ($1, $2); > > -- Get the oid of the row just inserted. > GET DIAGNOSTICS oid1 = RESULT_OID; > > retval := oid1; > > -- Everything has passed, return id as pk > RETURN retval; > END; > ' LANGUAGE 'plpgsql'; > > > Any help would be great! > > Thanks Again, > -p > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
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
On Mon, 2002-07-29 at 20:52, Peter Atkins wrote: > 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. That's the beauty of the nextval statement. The database internally sequences requests to it so that you're kept out of harm's way. Say process A called the function,and nextval returns 16. The function now continues on its way, but is not finished when process B then calls the function (before A is done), and nextval returns 17. So, then function called by process A returns 16, and the function called by process B returns 17. That means that unless the results of process B depend in some way upon the results of process A, there's no problem. -Ken -- Ken Corey CTO http://www.atomic-interactive.com 07720 440 731
You can retrieve the last inserted sequence value using:currval('t_task_task_id_seq')
This is connection safe, so you get the the last ID inserted by YOUR
connection.
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Ken Corey
> Sent: Monday, July 29, 2002 20:59
> To: Peter Atkins
> Cc: 'pgsql-sql@postgresql.org'
> Subject: Re: [SQL] 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>