PostgreSQL下一个值的序列? [英] PostgreSQL next value of the sequences?
问题描述
我使用PostgreSQL for Codeigniter网站。我使用杂货店添加,编辑和删除操作。在进行编辑或添加时,我想根据内容的ID动态重命名上传的文件。我可以使用grocery crud的 callback_after_upload
函数。
I am using PostgreSQL for my Codeigniter website. I am using grocery crud for add, edit and delete operations. While doing an edit or add, I want to rename an uploaded file dynamically based on the id of the content. I am able to do this using grocery crud's callback_after_upload
function.
我想在添加新内容时添加内容的下一个ID。我试图使用 nextval()
函数,但序列随着它增加。如何获得序列的最后一个值,而不使用 nextval()
function?
I want a next id of the content while adding a new content. I tried to use nextval()
function, but sequence gets incremented with it. How can get the last value of the sequence without using nextval()
function?
我可以这样做吗?
推荐答案
RETURNING
在现代PostgreSQL(8.2或更高版本)中,您可以通过单次往返到数据库:
INSERT INTO tbl(filename)
VALUES ('my_filename')
RETURNING tbl_id;
如果 filename
需要包含 tbl_id
(冗余),您仍然可以使用单个查询。
使用 lastval()
或更具体的 currval()
:
If filename
needs to include tbl_id
(redundantly), you can still use a single query.
Use lastval()
or the more specific currval()
:
INSERT INTO tbl (filename)
VALUES ('my_filename' || lastval()) -- or currval('tbl_tbl_id_seq')
RETURNING tbl_id;
此函数与
currval
相同,除了取代
序列名称作为参数,它获取最后
序列的值
This function is identical to
currval
, except that instead of taking the sequence name as an argument it fetches the value of the last sequence used bynextval
in the current session.
如果您拥有(或可以),您可以使用 nextval
具有)连接到表的多个序列(甚至通过触发器或其他副作用)确定方式是使用 currval('tbl_tbl_id_seq')
。
If you have (or can have) multiple sequences linked to the table (even by way of triggers or other side effects) the sure way is to use currval('tbl_tbl_id_seq')
.
字符串文字 'tbl_tbl_id_seq'在我的例子中应该是实际名称的序列,并转换为
regclass
如果在当前 search_path
。
The string literal 'tbl_tbl_id_seq'
in my example is supposed to be the actual name of the sequence and is cast to regclass
, which raises an exception if no sequence of that name can be found in the current search_path
.
tbl_tbl_id_seq
是表格 tbl
自动生成的缺省值,其中包含一个连续列 tbl_id
。但是没有保证。如果已定义,列默认值可以从任何序列中提取值。如果在创建表时采用默认名称,Postgres会根据硬编码算法选择下一个空闲名称。
tbl_tbl_id_seq
is the automatically generated default for a table tbl
with a serial column tbl_id
. But there are no guarantees. A column default can fetch values from any sequence if so defined. And if the default name is taken when creating the table, Postgres picks the next free name according to a hard-coded algorithm.
如果您不< < a href =http:// 的序列名称。 c> www.postgresql.org/docs/current/interactive/functions-info.html#FUNCTIONS-INFO-CATALOG-TABLE> pg_get_serial_sequence()
。甚至可以即时完成:
If you don't know the name of the sequence for a serial
column, look it up with the dedicated function pg_get_serial_sequence()
. Can even be done on the fly:
INSERT INTO tbl (filename)
VALUES ('my_filename' || currval(pg_get_serial_sequence('tbl', 'tbl_id'))
RETURNING tbl_id;
这篇关于PostgreSQL下一个值的序列?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!