Re: [GENERAL] PLSQL Question regarding multiple inserts
On Wed, Feb 25, 2004 at 04:11:37AM +, 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 <[EMAIL PROTECTED]> 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
pgpfvXJn9NvU2.pgp
Description: PGP signature
Re: [GENERAL] PLSQL Question regarding multiple inserts
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" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> 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" <[EMAIL PROTECTED]> wrote in message
> news:[EMAIL PROTECTED]
> > 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...
> >
> >
>
>
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[GENERAL] PLSQL Question regarding multiple inserts
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...
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [GENERAL] PLSQL Question regarding multiple inserts
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" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> 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...
>
>
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
