获取所有包含当前值的序列

Question

我有以下查询语句，它可以获取所有序列及其模式：

SELECT sequence_schema as schema, sequence_name as sequence
FROM information_schema.sequences
WHERE sequence_schema NOT IN ('topology', 'tiger')
ORDER BY 1, 2

我想通过类似 select last_value from [sequence]; 这样的语句获取每个序列名称的当前值。 我尝试了以下查询（和几种变化），但是由于语法不正确，所以它并不能正常工作：

DO $$
BEGIN
    EXECUTE 
        sequence_schema as schema,
        sequence_name as sequence,
        last_value
    FROM information_schema.sequences
    LEFT JOIN (
        EXECUTE 'SELECT last_value FROM ' || schema || '.' || sequence
    ) tmp
    ORDER BY 1, 2;
END
$$;

我发现了一些解决方案，它们创建函数来执行文本或在函数内部拼接查询并返回结果，但我更喜欢有一个单独的查询语句，可以根据需要运行和修改。

- GammaGames

---

@a_horse_with_no_name 12 - GammaGames

Answer 1

在Postgres 12中，您可以使用pg_sequences：

select schemaname as schema, 
       sequencename as sequence, 
       last_value
from pg_sequences

- user330315

Answer 2

您可以依赖于函数pg_sequence_last_value。

SELECT nspname as schema, 
       relname AS sequence_name,
       coalesce(pg_sequence_last_value(s.oid), 0) AS seq_last_value
FROM pg_class AS s
   JOIN pg_depend AS d ON d.objid = s.oid
   JOIN pg_attribute a ON d.refobjid = a.attrelid
                          AND d.refobjsubid = a.attnum
   JOIN pg_namespace nsp ON s.relnamespace = nsp.oid
WHERE s.relkind = 'S'
  AND d.refclassid = 'pg_class'::regclass
  AND d.classid = 'pg_class'::regclass
  AND nspname NOT IN ('topology', 'tiger')
ORDER BY 1,2 DESC;

- JGH

---

你能提供一下那个函数的链接吗？我认为它不是标准的Postgres函数。 - user330315

@a_horse_with_no_name 我相信是这样的...我的位于pg_catalog中，我不会自己添加任何内容。虽然我在文档中找不到它，但这里有另一个参考。这里是代码。 - JGH

那么看起来它没有被记录文档。 - user330315

这个邮件列表说该函数不应该直接调用，但有一个视图(pg_catalog.pg_sequences.last_value)使用了该函数: https://www.postgresql.org/message-id/9246.1569282009%40sss.pgh.pa.us - GammaGames

再多解释一点，这个函数名字有误导性 - 它不是最后一个“发行”的值，而是最后一个保存/缓存到磁盘的值。不能被信任用于消费。 - Alexi Theodore

Answer 3

这里提供一种不依赖于pg_sequences或pg_sequence_last_value的解决方案：

    CREATE OR REPLACE FUNCTION get_sequences()
      RETURNS TABLE (
        last_value bigint,
        sequence_schema text,
        sequence_name text
      )
      LANGUAGE plpgsql AS
    $func$
    DECLARE
        s RECORD;
    BEGIN
        FOR s IN SELECT t.sequence_schema, t.sequence_name
               FROM information_schema.sequences t
        LOOP
          RETURN QUERY EXECUTE format(
              'SELECT last_value, ''%1$s''::text, ''%2$s''::text FROM %1$I.%2$I',
              s.sequence_schema,
              s.sequence_name
          );
        END LOOP;
    END;
    $func$;

    SELECT * FROM get_sequences();

这将输出一个类似于以下表格的表格：

 last_value | sequence_schema |                     sequence_name
------------+-----------------+-------------------------------------------------------
          1 | public          | contact_infos_id_seq
          1 | media           | photos_id_seq
       2006 | company         | companies_id_seq
       2505 | public          | houses_id_seq
          1 | public          | purchase_numbers_id_seq
... etc

如果你使用的是现代版本的Postgres（我认为是10或更高版本），那么其他答案才能起作用。

- modulitos