SQL-question：返回插入querry的id [英] SQL-question: returning the id of an insert querry
问题描述
-----开始PGP签名消息-----
哈希：SHA1
我建立一个用户数据库，其中包含许多表格，用于保存
地址，电话号码等数据，这些数据由我保留的表格引用
单个用户。我的问题是，如何获取新的
插入地址的Id值以将其存储在引用用户表中：
（ a）INSERT INTO地址值（....）;
（b）INSERT INTO用户VALUES（名称，......，地址，......）;
其中地址应该保留Adress表中Id的值。
必须要做一个
SELECT id FROM FROM WHERE oid = oid_returned_by_insert（a）
或类似之后做插入（a）以获得正确的id
值，或者有更好的方法来做到这一点。 />
我用DBD / DBI用Perl编写我的应用程序
提前致谢，
Andreas Fromm
-----开始PGP SIGNATURE -----
版本：GnuPG v1.2.1（GNU / Linux）
评论：将GnuPG与Mozilla一起使用 - http://enigmail.mozdev.org
iD8DBQE / rhcdPkvkZVZzNY0RApm DAJ4k4MY / zKvH2862MuHSIjDtsmIs3QCfRzaR
0zDc1bIQAOMpLurvRZ2V8JY =
= kgaA
----- END PGP SIGNATURE -----
---------------------------（广播结束）---------------- -----------
提示9：如果您的
加入列的数据类型，计划程序将忽略您选择索引扫描的愿望不匹配
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hi,
Im building an user database with many tables keeping the data for the
Address, Phone numbers, etc which are referenced by a table where I keep
the single users. My question is, how do I get the "Id"-value of a newly
inserted address to store it in the referencing user table:
(a) INSERT INTO address VALUES (....);
(b) INSERT INTO users VALUES ( name, ... , address , ... );
where address should hold the value of the Id from the Adress table.
Do have to do an
SELECT id FROM address WHERE oid = oid_returned_by_insert(a)
or something like that after doing the insert(a) to get the correct id
value, or is there a better way to do this.
Im writing my app in Perl with DBD/DBI
Thanks in advance,
Andreas Fromm
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQE/rhcdPkvkZVZzNY0RApmDAJ4k4MY/zKvH2862MuHSIjDtsmIs3QCfRzaR
0zDc1bIQAOMpLurvRZ2V8JY=
=kgaA
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column''s datatypes do not match
推荐答案
在地址表上完成插入后，你可以使用
currval（''address_id_seq''）（或等效的）来获取ID。当然你有
来使用nextval（）作为原始插页。
希望这会有所帮助，
在Sun，2003年11月9日上午11:29:49 +0100，Andreas Fromm写道：
After you''ve done the insert on the address table, you can use
currval(''address_id_seq'') (or equivalent) to get the ID. Ofcourse you have
to have used nextval() for the original insert.
Hope this helps,
On Sun, Nov 09, 2003 at 11:29:49AM +0100, Andreas Fromm wrote:
我正在构建一个用户数据库，其中包含许多用于保存数据的表
地址，电话号码等由我保留单个用户的表引用。我的问题是，如何获取新插入地址的Id值以将其存储在引用用户表中：
（a）INSERT INTO地址值（。 ......）;
（b）INSERT INTO用户VALUES（名称，地址，......）;
其中地址应保存的值ID来自地址表。
SELECT id FROM FROM WHERE oid = oid_returned_by_insert（a）
或类似的东西（a）获得正确的id
值，或者有更好的方法来实现这一点。
我用DBD / DBI在Perl中编写应用程序
提前致谢，
Andreas Fromm
---------------- -----------（广播结束）---------------------------
提示9：如果您的
加入列的数据类型不符合
-
Martijn van，规划人员将忽略您选择索引扫描的愿望Oosterhout< kl ***** @ svan a.org> http://svana.org/kleptog/ 部队所需要的一切邪恶胜利是为了让人无所作为。 - 爱德蒙伯克
好人为不参与政治而付出的代价是由比自己更糟糕的人管理。 - 柏拉图
Hi,
Im building an user database with many tables keeping the data for the
Address, Phone numbers, etc which are referenced by a table where I keep
the single users. My question is, how do I get the "Id"-value of a newly
inserted address to store it in the referencing user table:
(a) INSERT INTO address VALUES (....);
(b) INSERT INTO users VALUES ( name, ... , address , ... );
where address should hold the value of the Id from the Adress table.
Do have to do an
SELECT id FROM address WHERE oid = oid_returned_by_insert(a)
or something like that after doing the insert(a) to get the correct id
value, or is there a better way to do this.
Im writing my app in Perl with DBD/DBI
Thanks in advance,
Andreas Fromm
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column''s datatypes do not match
--
Martijn van Oosterhout <kl*****@svana.org> http://svana.org/kleptog/ "All that is needed for the forces of evil to triumph is for enough good
men to do nothing." - Edmond Burke
"The penalty good people pay for not being interested in politics is to be
governed by people worse than themselves." - Plato
----- BEGIN PGP SIGNATURE -----
版本：GnuPG v1.0.6（GNU / Linux）
评论：有关信息，请参阅 http://www.gnupg.org
iD8DBQE / riFbY5Twig3Ge + YRArk / AKDU1nU6pzTtFVYjWXwsV0Dd2VtYagCgiBm4
SCBl2fXmByxYY8 + wHZ965mQ =
= rQMo
-----结束PGP签名-----
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org
iD8DBQE/riFbY5Twig3Ge+YRArk/AKDU1nU6pzTtFVYjWXwsV0Dd2VtYagCgiBm4
SCBl2fXmByxYY8+wHZ965mQ=
=rQMo
-----END PGP SIGNATURE-----
-----开始PGP签名消息-----
哈希：SHA1
Martijn van Oosterhout写道：
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Martijn van Oosterhout wrote:
在地址表上完成插入后，你可以使用
currval（''address_id_seq''）（或等效的）来获取ID。当然你已经使用了nextval（）作为原始插页。
希望这有帮助，
After you''ve done the insert on the address table, you can use
currval(''address_id_seq'') (or equivalent) to get the ID. Ofcourse you have
to have used nextval() for the original insert.
Hope this helps,
...试试吧。谢谢
Andreas Fromm
----- BEGIN PGP SIGNATURE -----
版本： GnuPG v1.2.1（GNU / Linux）
评论：在Mozilla中使用GnuPG - http://enigmail.mozdev.org
iD8DBQE / rkMwPkvkZVZzNY0RAnajAJ0ePCTi / UODhGAxOs5NuptZAT0tUgCgpNAz
Oqh8rM934O3SRRzv4Mh9S4I =
= E71z
----- END PGP SIGNATURE -----
--------------------- ------（播出结束）---------------------------
提示2：你可以使用取消注册命令一次性取消所有列表
（发送取消注册YourEmailAddressHere到 ma * ******@postgresql.org ）
...going to try it. Thanks
Andreas Fromm
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQE/rkMwPkvkZVZzNY0RAnajAJ0ePCTi/UODhGAxOs5NuptZAT0tUgCgpNAz
Oqh8rM934O3SRRzv4Mh9S4I=
=E71z
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)
2003年11月9日星期日03:13，Martijn van Oosterhout写道：
On Sunday 09 November 2003 03:13, Martijn van Oosterhout wrote:
在地址表上完成插入后，你可以使用
currval（''address_id_seq''） （或等效）获取ID。当然你必须使用nextval（）作为原始插入。
After you''ve done the insert on the address table, you can use
currval(''address_id_seq'') (or equivalent) to get the ID. Ofcourse you
have to have used nextval() for the original insert.
如果其他人在我获得currval之前插入了另一个地址怎么办？
我不幸运，对吗？
---------------------- -----（播出结束）---------------------------
提示6：你有没有？搜索了我们的列表档案？
http://archives.postgresql.org
What if someone else inserts another address before I get the currval?
I''m out of luck then, right?
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
这篇关于SQL-question：返回插入querry的id的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
