时间戳

首页 > 解决方案 > Postgresql - SQL 查询列出数据库中的所有序列

postgresql - Postgresql - SQL 查询列出数据库中的所有序列

问题描述

我想选择数据库中的所有序列，获取序列的架构、依赖表、表的架构、依赖列。

我尝试了以下查询：

  SELECT 
    ns.nspname AS sequence_schema_name, 
    s.relname AS sequence_name, 
    t_ns.nspname AS table_schema_name, 
    t.relname AS table_name, 
    a.attname AS column_name,
    s.oid,
    s.relnamespace,
    d.*,
    a.*
  FROM pg_class s
  JOIN pg_namespace ns 
  ON ns.oid = s.relnamespace
  left JOIN pg_depend d --
  ON d.objid = s.oid --TO FIX???
    AND d.classid = 'pg_class'::regclass --TO FIX???
    AND d.refclassid = 'pg_class'::regclass --TO FIX???
  left JOIN pg_class t 
  ON t.oid = d.refobjid --TO FIX???
  left JOIN pg_attribute a 
  ON a.attrelid = d.refobjid 
     AND a.attnum = d.refobjsubid
  left JOIN pg_namespace t_ns 
  ON t.relnamespace = t_ns.oid
  WHERE s.relkind = 'S' 
;

不幸的是，这个查询不能 100% 工作。查询过滤了一些序列。

我需要它进行进一步处理（在不同 ENV 上恢复数据后，我需要找到最大列值并将序列设置为 MAX+1）。

有人可以帮我吗？

标签： postgresqlsequenceplpgsqldynamic-sqlcatalog

解决方案

以下查询应该可以工作：

create table foo(id serial, v integer);
create table boo(id_boo serial, v integer);
create sequence omega;
create table bubu(id integer default nextval('omega'), v integer);

select sn.nspname as seq_schema,
       s.relname as seqname,
       st.nspname as tableschema,
       t.relname as tablename,
       at.attname as columname
  from pg_class s
  join pg_namespace sn on sn.oid = s.relnamespace
  join pg_depend d on d.refobjid = s.oid 
  join pg_attrdef a on d.objid = a.oid
  join pg_attribute at on at.attrelid = a.adrelid and at.attnum = a.adnum
  join pg_class t on t.oid = a.adrelid
  join pg_namespace st on st.oid = t.relnamespace
 where s.relkind = 'S'
   and d.classid = 'pg_attrdef'::regclass
   and d.refclassid = 'pg_class'::regclass;
┌────────────┬────────────────┬─────────────┬───────────┬───────────┐
│ seq_schema │    seqname     │ tableschema │ tablename │ columname │
╞════════════╪════════════════╪═════════════╪═══════════╪═══════════╡
│ public     │ foo_id_seq     │ public      │ foo       │ id        │
│ public     │ boo_id_boo_seq │ public      │ boo       │ id_boo    │
│ public     │ omega          │ public      │ bubu      │ id        │
└────────────┴────────────────┴─────────────┴───────────┴───────────┘
(3 rows)

要调用与序列相关的函数，您可以使用s.oidcolumn. 对于这种情况，它是序列唯一的 oid 标识符。您需要将其转换为regclass.

您请求的脚本可能如下所示：

do $$
declare
  r record;
  max_val bigint;
begin
  for r in
    select s.oid as seqoid,
           at.attname as colname,
           a.adrelid as reloid
      from pg_class s
      join pg_namespace sn on sn.oid = s.relnamespace
      join pg_depend d on d.refobjid = s.oid 
      join pg_attrdef a on d.objid = a.oid
      join pg_attribute at on at.attrelid = a.adrelid and at.attnum = a.adnum
     where s.relkind = 'S'
       and d.classid = 'pg_attrdef'::regclass 
       and d.refclassid = 'pg_class'::regclass
  loop
    -- probably lock here can be safer, in safe (single user) maintainance mode
    -- it is not necessary
    execute format('lock table %s in exclusive mode', r.reloid::regclass);

    -- expect usual one sequnce per table
    execute format('select max(%I) from %s', r.colname, r.reloid::regclass)
       into max_val;

    -- set sequence
    perform setval(r.seqoid, max_val + 1);
  end loop;
end;
$$

注意：%s在函数中使用for表名或序列名format是安全的，因为从Oid类型到regclass类型的转换生成安全字符串（每次需要时使用模式，每次需要时使用转义）。

推荐阅读