Обсуждение: prepare, execute & oids

Hello,

i've this kind of query:

PREPARE preparedInsert (varchar) AS
  INSERT INTO my_table (my_field)
  VALUES ($1);
  ');

and i use it with:

EXECUTE preparedInsert ('test');


I juste want to get the OID of the line inserted. Does anyone know how to do it ?

Thanks,

Phazon

On Tue, Aug 01, 2006 at 06:19:29PM +0200, phazon wrote:
> I juste want to get the OID of the line inserted. Does anyone know how to do
> it ?

It depends on the client interface.  What interface are you using?

OIDs are deprecated as row identifiers; the preferred method is to
use a sequence (serial column).  To get a sequence's value you can
use currval() or lastval() (the latter available in 8.1).

--
Michael Fuhr

I use PHP.
When I make juste a simple insert like

Insert Into my_table (my_field) values ('value')

I get back the OID of the inserted line.

But when i use the prepareInsert fonction, I get nothing back. That's a pprobleme becaus i need to take the primary key of the line inserted in order to make post-operations.

I can't use the curval of a sequence because it can be the value of another insert made by another user.

I use a prepare function because it's really faster (I need to insert many entries, nearly 5000) and i look for any way to make it faster and get the primary key of all the line inserted.

On Wed, Aug 02, 2006 at 09:25:43AM +0200, phazon wrote:
> When I make juste a simple insert like
>
> Insert Into my_table (my_field) values ('value')
>
> I get back the OID of the inserted line.

You really don't want to use OID, you want a sequence. (Hint: OIDs are
not really unique, not a primary key).

> I can't use the curval of a sequence because it can be the value of another
> insert made by another user.

currval() is the last value in *your current session*. It won't be
affected by other users.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

phazon wrote:
> I use PHP.
> When I make juste a simple insert like
>
> Insert Into my_table (my_field) values ('value')
>
> I get back the OID of the inserted line.
>
> But when i use the prepareInsert fonction, I get nothing back. That's a
> pprobleme becaus i need to take the primary key of the line inserted in
> order to make post-operations.
>
> I can't use the curval of a sequence because it can be the value of
> another insert made by another user.

currval is transaction safe, it's not a problem you need to worry about.

http://www.postgresql.org/docs/8.1/static/functions-sequence.html

--
Postgresql & php tutorials
http://www.designmagick.com/

On Wed, Aug 02, 2006 at 09:25:43AM +0200, phazon wrote:
> I use PHP.
> When I make juste a simple insert like
>
> Insert Into my_table (my_field) values ('value')
>
> I get back the OID of the inserted line.
>
> But when i use the prepareInsert fonction, I get nothing back. That's a
> pprobleme becaus i need to take the primary key of the line inserted in
> order to make post-operations.

The PHP source code has no prepareInsert function -- are you using
a third-party module?  If so then try contacting that module's
maintainer.  But as I mentioned previously and as Martijn pointed
out, OIDs aren't suitable as primary keys.  See the documentation
and FAQ:

http://www.postgresql.org/docs/8.1/interactive/datatype-oid.html
http://www.postgresql.org/docs/faqs.FAQ.html#item4.12

> I can't use the curval of a sequence because it can be the value of another
> insert made by another user.

As Martijn and Chris mentioned, currval() is safe in this respect.
Its behavior is documented and is the subject of an FAQ item:

http://www.postgresql.org/docs/8.1/interactive/functions-sequence.html
http://www.postgresql.org/docs/faqs.FAQ.html#item4.11.3

--
Michael Fuhr