Обсуждение: 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
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.
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.
2006/8/1, Michael Fuhr <mike@fuhr.org>:
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
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