Thread: PLSQL Question regarding multiple inserts

PLSQL Question regarding multiple inserts

From

"Humble Geek"

Date:

28 February 2004, 19:32:29

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...

Re: PLSQL Question regarding multiple inserts

From

"Greg Patnude"

Date:

28 February 2004, 19:32:31

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" <humblegeek@rogers.com> wrote in message
news:ZfV_b.60$Yf.1@twister01.bloor.is.net.cable.rogers.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...
>
>

Re: PLSQL Question regarding multiple inserts

From

"Humble Geek"

Date:

28 February 2004, 20:02:06

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" <gpatnude@hotmail.com> wrote in message
news:c1ir5r$2mhf$1@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" <humblegeek@rogers.com> wrote in message
> news:ZfV_b.60$Yf.1@twister01.bloor.is.net.cable.rogers.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...
> >
> >
>
>

Re: PLSQL Question regarding multiple inserts

From

Martijn van Oosterhout

Date:

28 February 2004, 20:30:25

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   <kleptog@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

Attachment

msg-32091-76440.dat

Re: PLSQL Question regarding multiple inserts

From

Richard Huxton

Date:

29 February 2004, 14:54:17

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

Re: PLSQL Question regarding multiple inserts

From

Bruno Wolff III

Date:

29 February 2004, 17:26:30

On Wed, Feb 25, 2004 at 04:11:37 +0000,
  Humble Geek <humblegeek@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.