pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedReturning PK of first insert for second insert use.
8+ messages / 4 participants
[nested] [flat]
* Returning PK of first insert for second insert use.
@ 2002-07-29 17:43 Peter Atkins <peter.atkins@NXCD.com>
0 siblings, 0 replies; 8+ messages in thread
From: Peter Atkins @ 2002-07-29 17:43 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Returning PK of first insert for second insert use.
@ 2002-07-29 18:32 Peter Atkins <peter.atkins@NXCD.com>
2002-07-29 18:59 ` Re: Returning PK of first insert for second insert use. Ken Corey <ken.corey@atomic-interactive.com>
2002-07-30 01:42 ` Re: Returning PK of first insert for second insert use. Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 2 replies; 8+ messages in thread
From: Peter Atkins @ 2002-07-29 18:32 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Returning PK of first insert for second insert use.
2002-07-29 18:32 Returning PK of first insert for second insert use. Peter Atkins <peter.atkins@NXCD.com>
@ 2002-07-29 18:59 ` Ken Corey <ken.corey@atomic-interactive.com>
2002-08-02 12:59 ` Re: Returning PK of first insert for second insert use. Cédric Dufour (Cogito Ergo Soft) <cedric.dufour@cogito-ergo-soft.com>
1 sibling, 1 reply; 8+ messages in thread
From: Ken Corey @ 2002-07-29 18:59 UTC (permalink / raw)
To: Peter Atkins <peter.atkins@NXCD.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Returning PK of first insert for second insert use.
2002-07-29 18:32 Returning PK of first insert for second insert use. Peter Atkins <peter.atkins@NXCD.com>
2002-07-29 18:59 ` Re: Returning PK of first insert for second insert use. Ken Corey <ken.corey@atomic-interactive.com>
@ 2002-08-02 12:59 ` Cédric Dufour (Cogito Ergo Soft) <cedric.dufour@cogito-ergo-soft.com>
0 siblings, 0 replies; 8+ messages in thread
From: Cédric Dufour (Cogito Ergo Soft) @ 2002-08-02 12:59 UTC (permalink / raw)
To: Ken Corey <ken.corey@atomic-interactive.com>; Peter Atkins <peter.atkins@NXCD.com>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Returning PK of first insert for second insert use.
2002-07-29 18:32 Returning PK of first insert for second insert use. Peter Atkins <peter.atkins@NXCD.com>
@ 2002-07-30 01:42 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
1 sibling, 0 replies; 8+ messages in thread
From: Christopher Kings-Lynne @ 2002-07-30 01:42 UTC (permalink / raw)
To: Peter Atkins <peter.atkins@NXCD.com>; pgsql-sql
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
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Returning PK of first insert for second insert use.
@ 2002-07-29 19:52 Peter Atkins <peter.atkins@NXCD.com>
2002-07-29 20:04 ` Re: Returning PK of first insert for second insert use. Ken Corey <ken.corey@atomic-interactive.com>
0 siblings, 1 reply; 8+ messages in thread
From: Peter Atkins @ 2002-07-29 19:52 UTC (permalink / raw)
To: Ken Corey <ken.corey@atomic-interactive.com>; +Cc: 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Returning PK of first insert for second insert use.
2002-07-29 19:52 Re: Returning PK of first insert for second insert use. Peter Atkins <peter.atkins@NXCD.com>
@ 2002-07-29 20:04 ` Ken Corey <ken.corey@atomic-interactive.com>
0 siblings, 0 replies; 8+ messages in thread
From: Ken Corey @ 2002-07-29 20:04 UTC (permalink / raw)
To: Peter Atkins <peter.atkins@NXCD.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Returning PK of first insert for second insert use.
@ 2002-07-29 20:20 Peter Atkins <peter.atkins@NXCD.com>
0 siblings, 0 replies; 8+ messages in thread
From: Peter Atkins @ 2002-07-29 20:20 UTC (permalink / raw)
To: Ken Corey <ken.corey@atomic-interactive.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2002-08-02 12:59 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-07-29 17:43 Returning PK of first insert for second insert use. Peter Atkins <peter.atkins@NXCD.com>
2002-07-29 18:32 Returning PK of first insert for second insert use. Peter Atkins <peter.atkins@NXCD.com>
2002-07-29 18:59 ` Ken Corey <ken.corey@atomic-interactive.com>
2002-08-02 12:59 ` Cédric Dufour (Cogito Ergo Soft) <cedric.dufour@cogito-ergo-soft.com>
2002-07-30 01:42 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-07-29 19:52 Re: Returning PK of first insert for second insert use. Peter Atkins <peter.atkins@NXCD.com>
2002-07-29 20:04 ` Ken Corey <ken.corey@atomic-interactive.com>
2002-07-29 20:20 Re: Returning PK of first insert for second insert use. Peter Atkins <peter.atkins@NXCD.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