By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,799 Members | 1,294 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,799 IT Pros & Developers. It's quick & easy.

PLSQL Question regarding multiple inserts

P: n/a
Hi all. Quick and perhaps silly question, but...

I am using Pg 7.3. I am writing a function using pgplsql. This function will
perform multiple inserts. Let's say two of the inserts are as follows:

-- id is primary key
insert into users (id, username) values (nextval('someSeq'),'somename');

-- id is also a PK
insert into log (id, uid, message) values (nextval('someOtherSeq'),XXX,'New
Account');

Assume XXX is the id from the first insert. How do I get that number? Not
currval('someSeq') - 'cause someone else may have performed an insert - but
the id for that specific insert.

Thanks,

HG

PS: Sorry for the cross-post...
Nov 22 '05 #1
Share this Question
Share on Google+
5 Replies

P: n/a
That's the hard way....

You'd be better off redefining your table structures so that postgreSQL
handles the primary keys automatically...

CREATE TABLE test (

id integer primary key not null default nextval('test_seq'),
log varchar(32) NOT NULL,
message text

) WITH OIDS;

Using this type of table def will automatically create the sequence for
you -- and always ge thte next value when you do an insert -- ensuring that
you dont have duplicate...

so you would:

INSERT INTO test ('log', 'message');

then

SELECT * FROM test;

would give you

id, log and message.

--
Greg Patnude / The Digital Demention
2916 East Upper Hayden Lake Road
Hayden Lake, ID 83835
(208) 762-0762

"Humble Geek" <hu********@rogers.com> wrote in message
news:Zf***********@twister01.bloor.is.net.cable.ro gers.com...
Hi all. Quick and perhaps silly question, but...

I am using Pg 7.3. I am writing a function using pgplsql. This function will perform multiple inserts. Let's say two of the inserts are as follows:

-- id is primary key
insert into users (id, username) values (nextval('someSeq'),'somename');

-- id is also a PK
insert into log (id, uid, message) values (nextval('someOtherSeq'),XXX,'New Account');

Assume XXX is the id from the first insert. How do I get that number? Not
currval('someSeq') - 'cause someone else may have performed an insert - but the id for that specific insert.

Thanks,

HG

PS: Sorry for the cross-post...

Nov 22 '05 #2

P: n/a
Thanks Greg.

That does help me some, however, I am stuck with this database (I have
inherited) - it has over a hundred tables, and while I may look into
converting it at some point, it is just unfeasible at this junction. So
where can I look to find the hard way? :)

HG
"Greg Patnude" <gp******@hotmail.com> wrote in message
news:c1***********@news.hub.org...
That's the hard way....

You'd be better off redefining your table structures so that postgreSQL
handles the primary keys automatically...

CREATE TABLE test (

id integer primary key not null default nextval('test_seq'),
log varchar(32) NOT NULL,
message text

) WITH OIDS;

Using this type of table def will automatically create the sequence for
you -- and always ge thte next value when you do an insert -- ensuring that you dont have duplicate...

so you would:

INSERT INTO test ('log', 'message');

then

SELECT * FROM test;

would give you

id, log and message.

--
Greg Patnude / The Digital Demention
2916 East Upper Hayden Lake Road
Hayden Lake, ID 83835
(208) 762-0762

"Humble Geek" <hu********@rogers.com> wrote in message
news:Zf***********@twister01.bloor.is.net.cable.ro gers.com...
Hi all. Quick and perhaps silly question, but...

I am using Pg 7.3. I am writing a function using pgplsql. This function

will
perform multiple inserts. Let's say two of the inserts are as follows:

-- id is primary key
insert into users (id, username) values (nextval('someSeq'),'somename');

-- id is also a PK
insert into log (id, uid, message) values

(nextval('someOtherSeq'),XXX,'New
Account');

Assume XXX is the id from the first insert. How do I get that number? Not currval('someSeq') - 'cause someone else may have performed an insert -

but
the id for that specific insert.

Thanks,

HG

PS: Sorry for the cross-post...


Nov 22 '05 #3

P: n/a
On Wed, Feb 25, 2004 at 04:11:37AM +0000, Humble Geek wrote:
Assume XXX is the id from the first insert. How do I get that number? Not
currval('someSeq') - 'cause someone else may have performed an insert - but
the id for that specific insert.
Read the documentation carefully, currval() does what you want, it
isn't affected by concurrent inserts.

--
Martijn van Oosterhout <kl*****@svana.org> http://svana.org/kleptog/ If the Catholic church can survive the printing press, science fiction
will certainly weather the advent of bookwarez.
http://craphound.com/ebooksneitherenorbooks.txt - Cory Doctorow


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFAQPylY5Twig3Ge+YRAn0KAKCXB1DHro/fOadKfpBulqua23+yoQCgq2V8
+mVDpy+6fNY3E5gf+Z1ZiSs=
=160B
-----END PGP SIGNATURE-----

Nov 23 '05 #4

P: n/a
On Wednesday 25 February 2004 04:11, Humble Geek wrote:
Hi all. Quick and perhaps silly question, but...

I am using Pg 7.3. I am writing a function using pgplsql. This function
will perform multiple inserts. Let's say two of the inserts are as follows:

-- id is primary key
insert into users (id, username) values (nextval('someSeq'),'somename');
insert into log (id, uid, message) values (nextval('someOtherSeq'),XXX,'New
Account');

Assume XXX is the id from the first insert. How do I get that number? Not
currval('someSeq') - 'cause someone else may have performed an insert -
but the id for that specific insert.


Sequences are safe to use in multi-user environments. That is, currval() will
return the most recent value nextval() returned *in this connection*.
Wouldn't be much use otherwise.

The easiest way to demonstrate this is to open two psql sessions and try it
for yourself.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #5

P: n/a
On Wed, Feb 25, 2004 at 04:11:37 +0000,
Humble Geek <hu********@rogers.com> wrote:
Hi all. Quick and perhaps silly question, but...

I am using Pg 7.3. I am writing a function using pgplsql. This function will
perform multiple inserts. Let's say two of the inserts are as follows:

-- id is primary key
insert into users (id, username) values (nextval('someSeq'),'somename');

-- id is also a PK
insert into log (id, uid, message) values (nextval('someOtherSeq'),XXX,'New
Account');

Assume XXX is the id from the first insert. How do I get that number? Not
currval('someSeq') - 'cause someone else may have performed an insert - but
the id for that specific insert.


currval is per backend, so it is safe to use in the second insert.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.