PostgreSQL Inserted OID

ruby-talk
1

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!

2

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

3

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

4

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?

···

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!

5

nexus wyrze¼bi³(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± da³o nam nowoczesne spo³eczeñstwo, jest
                 niesamowita wrêcz ³atwo¶æ unikania kontaktów z nim

6

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

Interesting...

-g.

7

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.

···

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

nexus wyrze¼bi³(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

8

nexus wyrze¼bi³(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± da³o nam nowoczesne spo³eczeñstwo, jest
                 niesamowita wrêcz ³atwo¶æ unikania kontaktów z nim