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... 5 3553
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...
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...
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-----
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: David |
last post by:
I am learning plsql. I would like to run a stored procedure to
calculate my bank account value by predicted 10% annual growth rate.
Below is my plsql that is having problems. Your help is highly...
|
by: Joe |
last post by:
Hey,
I'm going to give some background on my situation in case anyone can
point out a way around my problem altogether... for the problem
itself, please skip to the bottom of the post. thanks....
|
by: Primo |
last post by:
Hello,
I am building a data management application with the following processes:
Process 1 is a Windows service which uses FileSystemWatcher to monitor a
directory.
Process 2 opens a file...
|
by: jaYPee |
last post by:
I have search a lot of thread in google newsgroup and read a lot of
articles but still i don't know how to update the dataset that has 3
tables.
my 3 tables looks like the 3 tables from...
|
by: dkarthick |
last post by:
How to execute UTL_FILE Package in plsql?
|
by: Michel Esber |
last post by:
Hello,
Environment: db2 V8 FP 13 LUW
Our application currently uses:
insert into table values ('A'),('B'),...('Z')
We have used CLI arrays inserts (1000 array and commit size) and...
|
by: anuptosh |
last post by:
Hi,
I have been trying to run the below example to get a Oracle Array as an output from a Java code. This is an example I have found on the web. But, the expected result is that the code should...
|
by: sybrandb |
last post by:
"Jorge Pinto" <jorgep@sympatico.cawrote in message news:<L2HQa.3116$104.264170@news20.bellglobal.com>...
utl_smtp is only a wrapper for a java procedure. Need I say more.
And oh yes, you may need...
|
by: bonneylake |
last post by:
Hey Everyone,
Well recently i been inserting multiple fields for a section in my form called "serial". Well now i am trying to insert multiple fields for the not only the serial section but also...
|
by: MeoLessi9 |
last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: Aftab Ahmad |
last post by:
Hello Experts!
I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
|
by: Aftab Ahmad |
last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below.
Dim IE As Object
Set IE =...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
| |