How to get seq after insert

The safest way is to select the nextval('seq_name') and then insert using
this value.

-----Original Message-----
From: Brian [SMTP:signal@shreve.net]
Sent: Wednesday, April 14, 1999 2:59 PM
To: pgsql-general@postgreSQL.org
Subject: [GENERAL] How to get seq after insert

I have a sequence in a table that increments upon insert. After
doing the
insert, is their a way (function maybe?) to get the sequences value
without having to do another select?

Brian

-----------------------------------------------------
Brian Feeny (BF304) signal@shreve.net
318-222-2638 x 109 http://www.shreve.net/~signal
Network Administrator ShreveNet Inc. (ASN 11881)

On Wed, 14 Apr 1999, Michael Davis wrote:

The safest way is to select the nextval('seq_name') and then insert using
this value.

I understand, I just thought something like, I do the insert, and then
grab the value with:

$insertid = $sth->{'insertid'};

assuming field "insertid" was the one being updated by the sequence, but
that doenst seem to work.

-----Original Message-----
From: Brian [SMTP:signal@shreve.net]
Sent: Wednesday, April 14, 1999 2:59 PM
To: pgsql-general@postgreSQL.org
Subject: [GENERAL] How to get seq after insert

I have a sequence in a table that increments upon insert. After
doing the
insert, is their a way (function maybe?) to get the sequences value
without having to do another select?

Brian

-----------------------------------------------------
Brian Feeny (BF304) signal@shreve.net
318-222-2638 x 109 http://www.shreve.net/~signal
Network Administrator ShreveNet Inc. (ASN 11881)

-----------------------------------------------------
Brian Feeny (BF304) signal@shreve.net
318-222-2638 x 109 http://www.shreve.net/~signal
Network Administrator ShreveNet Inc. (ASN 11881)

On Wed, 14 Apr 1999, Michael Davis wrote:

The safest way is to select the nextval('seq_name') and then insert using
this value.

I understand, I just thought something like, I do the insert, and then
grab the value with:

$insertid = $sth->{'insertid'};

The OID is returned from the insert. Use that, or use it to look up the
field you want.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

On Thu, 15 Apr 1999, Bruce Momjian wrote:

On Wed, 14 Apr 1999, Michael Davis wrote:

The safest way is to select the nextval('seq_name') and then insert using
this value.

I understand, I just thought something like, I do the insert, and then
grab the value with:

$insertid = $sth->{'insertid'};

The OID is returned from the insert. Use that, or use it to look up the
field you want.

Actually, I think the number of rows inserted is returned from the
insert, or an error code (a negative number). But the OID can be
obtained by $oid = sth->{'pg_oid_status'};

--
Karl DeBisschop <kdebisschop@spaceheater.infoplease.com>
617.832.0332 (Fax: 617.956.2696)

Information Please - your source for FREE online reference
http://www.infoplease.com - Your Ultimate Fact Finder
http://kids.infoplease.com - The Great Homework Helper

I think writing SQL as a portable as possible to cover eventual upgrades to
larger scale databases is a primary concern. The Selecting of a sequence is
more portable across various databases...

-----Original Message-----
From: Michael Davis <michael.davis@tvguide.com>
To: 'Brian' <signal@shreve.net>; pgsql-general@postgreSQL.org
<pgsql-general@postgreSQL.org>
Date: Wednesday, April 14, 1999 4:37 PM
Subject: RE: [GENERAL] How to get seq after insert

At 00:39 +0300 on 15/04/1999, Michael Davis wrote:

The safest way is to select the nextval('seq_name') and then insert using
this value.

No, actually, this is the unsafest way. This means that the logic is in the
frontend, not the backend. Besides, one can define the sequence as
read-only for the user who uses the database, but write for the one who
created the table that uses it, so that the user can't change the sequence
out of line.

To make a long story short, the best way is to let the insert statement use
the defaulet, and then use currval( 'seq_name' ). This gives you the last
value given in the current session. It is multiuser-safe, etc.

This was on the SQL list a couple of weeks ago. And by the way, the SQL
list is the proper list for this issue.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma

If two users insert into the same table at the same time, one of the users
will get the wrong currval back. I still believe that getting the nextval
first is the safest way. It may create holes when an insert fails, but this
is better than getting back the wrong curval.

-----Original Message-----
From: Herouth Maoz [SMTP:herouth@oumail.openu.ac.il]
Sent: Tuesday, April 20, 1999 5:04 AM
To: Michael Davis; 'Brian'; pgsql-general@postgreSQL.org
Subject: RE: [GENERAL] How to get seq after insert

At 00:39 +0300 on 15/04/1999, Michael Davis wrote:

The safest way is to select the nextval('seq_name') and then

insert using

this value.

No, actually, this is the unsafest way. This means that the logic is
in the
frontend, not the backend. Besides, one can define the sequence as
read-only for the user who uses the database, but write for the one
who
created the table that uses it, so that the user can't change the
sequence
out of line.

To make a long story short, the best way is to let the insert
statement use
the defaulet, and then use currval( 'seq_name' ). This gives you the
last
value given in the current session. It is multiuser-safe, etc.

This was on the SQL list a couple of weeks ago. And by the way, the
SQL
list is the proper list for this issue.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma

Michael J Davis wrote:

If two users insert into the same table at the same time, one of the users
will get the wrong currval back. I still believe that getting the nextval
first is the safest way. It may create holes when an insert fails, but this
is better than getting back the wrong curval.

No, as Herouth pointed out, currval is multiuser-safe: it returns the
last value given in the current session, and every user get's their own
session. I just tried it out in two psql sessions to a test sequence -
no matter how many calls to nextval I do in one window, the currval in
each gives the write answer.

Ross

-----Original Message-----
From: Herouth Maoz [SMTP:herouth@oumail.openu.ac.il]>
To make a long story short, the best way is to let the insert
statement use
the defaulet, and then use currval( 'seq_name' ). This gives you the
last
value given in the current session. It is multiuser-safe, etc.

--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005

Hmm, Ross needs to break for lunch - his brain is runing out of glucose!

Ross J. Reedstrom wrote:

No, as Herouth pointed out, currval is multiuser-safe: it returns the
last value given in the current session, and every user get's their own

gets

session. I just tried it out in two psql sessions to a test sequence -
no matter how many calls to nextval I do in one window, the currval in
each gives the write answer.

right or correct

Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005

Cool!!! I did not know this. Thanks for verifying how this works. I will
change my application to use currval instead of nextval.

-----Original Message-----
From: Ross J. Reedstrom [SMTP:reedstrm@rice.edu]
Sent: Tuesday, April 20, 1999 10:33 AM
To: pgsql-general@postgreSQL.org
Subject: Re: [GENERAL] How to get seq after insert

Michael J Davis wrote:

If two users insert into the same table at the same time, one of

the users

will get the wrong currval back. I still believe that getting the

nextval

first is the safest way. It may create holes when an insert

fails, but this

is better than getting back the wrong curval.

No, as Herouth pointed out, currval is multiuser-safe: it returns
the
last value given in the current session, and every user get's their
own
session. I just tried it out in two psql sessions to a test sequence
-
no matter how many calls to nextval I do in one window, the currval
in
each gives the write answer.

Ross

-----Original Message-----
From: Herouth Maoz [SMTP:herouth@oumail.openu.ac.il]>
To make a long story short, the best way is to let the

insert

statement use
the defaulet, and then use currval( 'seq_name' ). This

gives you the

last
value given in the current session. It is multiuser-safe,

etc.

--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005

What's the best way to do this in postgres? (basicly finding the type of
objects).

I want to run a web site with different types of content - question and
answers, stories etc. I propose an object hierarchy...
webobject (title, body)
question inherits webobject
story (image) inherits (webobject).

The idea being you could have a search screen that searches questions
AND stories with the one SELECT query.

But then each result would have a link to examine the body of the search
result. But different types of objects would have different URLs to
display that content.

So basicly I need to know the type of objects returned.

I am loath to store the object type inside the object because it is
wasteful. PG obviously already knows the type of objects, the question
is how to get at that info.

--retrieve column information...

SELECT a.attnum, a.attname, t.typname, a.attlen,
a.atttypmod, a.attnotnull, a.atthasdef
FROM pg_class c, pg_attribute a, pg_type t
WHERE c.relname = 'comuni'
and a.attnum > 0
and a.attrelid = c.oid
and a.atttypid = t.oid
ORDER BY attnum ;
attnum|attname |typname|attlen|atttypmod|attnotnull|atthasdef
------+--------------+-------+------+---------+----------+---------
1|istat |bpchar | -1| 10|t |f
2|nome |bpchar | -1| 54|t |f
3|provincia |bpchar | -1| 6|f |f
4|codice_fiscale|bpchar | -1| 8|f |f
5|cap |bpchar | -1| 9|f |f
6|regione |bpchar | -1| 7|f |f
7|distretto |bpchar | -1| 8|f |f
(7 rows)

Jos�

Chris Bitmead ha scritto:

Umm. I need to know the type of the _object_, not the types of the
attributes contained therein.

Jos� Soares wrote:

--retrieve column information...

SELECT a.attnum, a.attname, t.typname, a.attlen,
a.atttypmod, a.attnotnull, a.atthasdef
FROM pg_class c, pg_attribute a, pg_type t
WHERE c.relname = 'comuni'
and a.attnum > 0
and a.attrelid = c.oid
and a.atttypid = t.oid
ORDER BY attnum ;
attnum|attname |typname|attlen|atttypmod|attnotnull|atthasdef
------+--------------+-------+------+---------+----------+---------
1|istat |bpchar | -1| 10|t |f
2|nome |bpchar | -1| 54|t |f
3|provincia |bpchar | -1| 6|f |f
4|codice_fiscale|bpchar | -1| 8|f |f
5|cap |bpchar | -1| 9|f |f
6|regione |bpchar | -1| 7|f |f
7|distretto |bpchar | -1| 8|f |f
(7 rows)

Jos�

Chris Bitmead ha scritto:

What's the best way to do this in postgres? (basicly finding the
type of
objects).

I want to run a web site with different types of content - question
and
answers, stories etc. I propose an object hierarchy...
webobject (title, body)
question inherits webobject
story (image) inherits (webobject).

The idea being you could have a search screen that searches
questions
AND stories with the one SELECT query.

But then each result would have a link to examine the body of the
search
result. But different types of objects would have different URLs to
display that content.

So basicly I need to know the type of objects returned.

I am loath to store the object type inside the object because it is
wasteful. PG obviously already knows the type of objects, the
question
is how to get at that info.

--
Chris Bitmead
http://www.bigfoot.com/~chris.bitmead
mailto:chris.bitmead@bigfoot.com