可以用以下函数来列出当前DB的所有序列的最后一个值
CREATE OR REPLACE FUNCTION fun_list_seq()
RETURNS TABLE(schema_name text,seq_name text,last_val bigint,increment_by bigint)
AS
$body$
DECLARE
sequence_name varchar(255);
schema_name text;
BEGIN
FOR sequence_name,schema_name in SELECT relname,nspname FROM pg_class a,pg_namespace b WHERE a.relnamespace = b.oid and a.relkind = 'S'
LOOP
RETURN QUERY EXECUTE 'SELECT ' ||quote_literal(schema_name)||'::text,'|| quote_literal(sequence_name) || '::text,last_value,increment_by FROM ' || quote_ident(sequence_name) ;
END LOOP;
RETURN;
END
$body$
LANGUAGE 'plpgsql';
实现效果
[postgres@localhost ~]$ psql db_ken
psql (9.4beta2)
Type "help" for help.
db_ken=# select * from fun_list_seq() ;
schema_name | seq_name | last_val | increment_by
-------------+----------------+----------+--------------
public | t2_id_seq | 49 | 2
public | tbl_ken_id_seq | 20 | 1
public | t_id_seq | 49 | 2
bucardo | t_id_seq | 49 | 2
(4 rows)
db_ken=# \q