[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

comp.lang.ruby

PostgreSQL Inserted OID

nexus

6/23/2005 12:16:00 AM

Does anyone know how to get the inserted OID following an insert
statement in PostgreSQL (using DBI and/or Postgres Lib)? I'm not having
any luck finding the answer.

Thanks in advance!
7 Answers

Aleksi

6/23/2005 5:53:00 AM

0

nexus wrote:
> Does anyone know how to get the inserted OID following an insert
> statement in PostgreSQL (using DBI and/or Postgres Lib)? I'm not having
> any luck finding the answer.

I don't know what is the "right" answer, but I know how one piece of
software handles the issue. Og does this in two separate statements:

1) res = store.conn.exec "SELECT nextval('#{klass::OGSEQ}')"
2) store.conn.exec("INSERT INTO #{klass::OGTABLE} (#{props.collect {|p|
p.symbol.to_s}.join(',')}) VALUES (#{values})").clear

Which basically means there's a sequence for each of the managed classes
together with the table objects are really kept for persistence.

Maybe this solution is viable for you too. Maybe all this should be in
one transaction. Maybe this should be a stored procedure (as George has
put in a code as a todo task). Maybe you should do something completely
different.

- Aleksi

Digikata@gmail.com

6/23/2005 8:23:00 AM

0

nexus wrote:
> Does anyone know how to get the inserted OID following an insert
> statement in PostgreSQL (using DBI and/or Postgres Lib)? I'm not having
> any luck finding the answer.
>
> Thanks in advance!

Are you using the ruby-postgres library? It looks like fairly minor
work to add a method to call the PQoidvalue function in the C interface
to retreive the oid value following the insert.

- alan

nexus

6/23/2005 4:09:00 PM

0

On Wed, 22 Jun 2005 18:16:01 -0600, nexus wrote:

> Does anyone know how to get the inserted OID following an insert
> statement in PostgreSQL (using DBI and/or Postgres Lib)? I'm not having
> any luck finding the answer.
>
> Thanks in advance!

I studied the psql.rb code included with the postgres driver and came up
with the following after noticing that the OID was being printed out
following an insert:

db = PGconn.connect(...)
results = db.exec("insert...")
oid = /([0-9]+)/.match(results.cmdstatus)

Does anyone know how to get the oid using the DBI library? Could it be
done with .func() calls?

hubert depesz lubaczewski

6/23/2005 5:47:00 PM

0

nexus wyrze?bi3(a):
> Does anyone know how to get the oid using the DBI library? Could it be
> done with .func() calls?

would you be kind enough to tell us why do you think you need oids?
usage of oids is deprecated and dangerous. and since they're not unique
i frankly see no point in getting them.

depesz

--
*------------------------------------------------------------------*
najwspanialsz? rzecz? jak? da3o nam nowoczesne spo3eczenstwo, jest
niesamowita wrecz 3atwo?a unikania kontaktów z nim

nexus

6/23/2005 9:43:00 PM

0

On Thu, 23 Jun 2005 19:47:16 +0200, hubert depesz lubaczewski wrote:

> nexus wyrze?bi3(a):
>> Does anyone know how to get the oid using the DBI library? Could it be
>> done with .func() calls?
>
> would you be kind enough to tell us why do you think you need oids?
> usage of oids is deprecated and dangerous. and since they're not unique
> i frankly see no point in getting them.
>
> depesz

You are absolutely correct and raise a valid point: When you want to find
the generated primary key value from a sequence column, the correct method
is to select nextval from the sequence then insert the key yourself.

However, for quick and dirty one time imports (ie. from a CSV) into a new
table, you can safely use the OID as a unique row reference provided you
are not inserting more than 4 billion rows into the new table. Its a dirty
hack but since the oid is returned as part of the insert query, you cut
your queries in half and use less code. Our data import routines are
always one offs so I'm just looking for the simplest/quickest way to get
the data imported.

hubert depesz lubaczewski

6/24/2005 7:33:00 AM

0

nexus wyrze?bi3(a):
> always one offs so I'm just looking for the simplest/quickest way to get
> the data imported.

i assume that you do it more or less this way:
insert into tablea (...) values (...);
getoid();
insert into tableb (...) values (this_oid, ...);

in this case - just use sequences, and to:
insert into tablea (...) values (...);
insert into tableb (...) values (currval('name_of_sequence_from_tablea'), ...);

depesz

--
*------------------------------------------------------------------*
najwspanialsz? rzecz? jak? da3o nam nowoczesne spo3eczenstwo, jest
niesamowita wrecz 3atwo?a unikania kontaktów z nim

George Moschovitis

6/24/2005 9:46:00 AM

0

> db = PGconn.connect(...)
> results = db.exec("insert...")
> oid = /([0-9]+)/.match(results.cmdstatus)

Interesting...

-g.