Thread: RE: [GENERAL] 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
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
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:
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.
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