如何确定序列是否在此会话中初始化? [英] How to find out if a sequence was initialized in this session?

问题描述

我需要在函数中读取序列的当前值.但是，在每个会话中我第一次尝试使用 currval() 时，出现以下错误:

I need to read the current value of a sequence in a function. However, for the first time in each session I try to use currval(), I get following error:

currval of sequence "foo_seq" is not yet defined in this session

提示那些可能通过谷歌搜索发现这个错误的人:你需要初始化每个会话的序列，或者通过 nextval() 或 setval().

Hint for those who might find this question by googling for this error: you need to initialize the sequence for each session, either by nextval() or setval().

我可以使用类似 lastval() 甚至 setval('your_table_id_seq', (SELECT MAX(id) FROM your_table)); 代替，但这似乎比简单的 currval() 似乎容易出现间隙或慢.我的目标是避免差距和不一致(我知道一些值将被手动添加)，因此在逻辑处理它们之前使用 nextval() 对我的目的来说并不理想.无论如何，我都需要它来初始化会话的序列，但我更愿意做这样的事情:

I could use something like lastval() or even setval('your_table_id_seq', (SELECT MAX(id) FROM your_table)); instead, but this seems seems either prone to gaps or slower than simple currval(). My aim is to avoid gaps and inconsistencies (I know some of the values will be added manually), so using nextval() before logic handling them is not ideal for my purpose. I would need this to initialize the sequence for the session anyway, but I would prefer to do something like this:

--start of the function here
IF is_not_initialized THEN
  SELECT setval('foo_seq', (SELECT MAX(id) FROM bar_table)) INTO _current;
ELSE
  SELECT currval('foo_seq') INTO _current;
END IF; 
--some magic with the _current variable and nextvalue() on the right position

关键是我不知道is_not_initialized"会是什么样子以及它是否可能.有什么功能或其他技巧可以做到吗?

实际上，我的计划是让每组客户在正确的顺序、根本没有顺序和我现在要求的奇怪的类似顺序的东西"之间进行选择.即使客户想要这样一个奇怪的序列，它也只会用于需要它的列——通常是因为有一些模拟数据，我们需要将它们的键(通常是几乎无间隙的序列)存储到数据库中以实现向后兼容.

Actually, my plan is to let each group of customers choose between proper sequence, no sequence at all, and the strange "something like a sequence" I'm asking for now. Even if the customer wanted such a strange sequence, it would be used only for the columns where it is needed - usually because there are some analog data and we need to store their keys (usually almost gapless sequence) into the DB for backward compatibility.

无论如何，您说得对，这几乎不是正确的解决方案，并且在这些情况下，没有任何顺序可能比这种混乱的解决方法更好，因此我会再次考虑(并与客户讨论)是否真的需要它.

Anyway, you are right that this is hardly proper solution and that no sequence might be better than such a messy workaround in those situations, so I'll think (and discuss with customers) again whether it is really needed.

推荐答案

Craig、a_horse 和 pozs 提供了可以帮助您理解使用序列的原则的信息.除了您将如何使用它的问题之外，这里有一个函数，如果序列已初始化，则返回当前值，否则返回 null.

Craig, a_horse and pozs have provided information which can help you understand principles of using sequences. Apart from the question how are you going to use it, here is a function which returns current value of a sequence if it has been initialized or null otherwise.

如果序列 seq 尚未初始化，currval(seq) 会引发 sqlstate 55000 的异常.

If a sequence seq has not been initialized yet, currval(seq) raises exception with sqlstate 55000.

create or replace function current_seq_value(seq regclass)
returns integer language plpgsql 
as $$
begin
    begin
        return (select currval(seq));
    exception
        when sqlstate '55000' then return null;
    end;
end $$;

select current_seq_value('my_table_id_seq')

这篇关于如何确定序列是否在此会话中初始化?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！