PostgreSQL小知识--psql元命令显示的对象不全

知识点

• pg_catalog.pg_table_is_visible 查询函数
  表、视图、索引、序列是否在搜索路径中可见，这个函数需要使用 OID 来标识要被检查的对象，如果想通过对象名检查，那么可以使用 OID 的其中一个别名类型 regclass。
• search_path 搜索路径参数

示例

使用 psql 的元命令 \ds 查询当前数据库下的序列，发现显示不全。

postgis-# \ds
                List of relations
 Schema |       Name        |   Type   |  Owner   
--------+-------------------+----------+----------
 public | us_gaz_id_seq     | sequence | postgres
 public | us_lex_id_seq     | sequence | postgres
 public | us_rules_id_seq   | sequence | postgres
 tiger  | addr_gid_seq      | sequence | postgres
 tiger  | addrfeat_gid_seq  | sequence | postgres
 tiger  | bg_gid_seq        | sequence | postgres
 tiger  | county_gid_seq    | sequence | postgres
 tiger  | cousub_gid_seq    | sequence | postgres
 tiger  | edges_gid_seq     | sequence | postgres
 tiger  | faces_gid_seq     | sequence | postgres
 tiger  | featnames_gid_seq | sequence | postgres
 tiger  | pagc_gaz_id_seq   | sequence | postgres
 tiger  | pagc_lex_id_seq   | sequence | postgres
 tiger  | pagc_rules_id_seq | sequence | postgres
 tiger  | place_gid_seq     | sequence | postgres
 tiger  | state_gid_seq     | sequence | postgres
 tiger  | tabblock_gid_seq  | sequence | postgres
 tiger  | tract_gid_seq     | sequence | postgres
 tiger  | zcta5_gid_seq     | sequence | postgres
(19 rows)

# 加上序列名可以查询到
postgis-# \ds gis.gis_osm_railways_free_1_id_seq
                       List of relations
 Schema |              Name              |   Type   |  Owner   
--------+--------------------------------+----------+----------
 gis    | gis_osm_railways_free_1_id_seq | sequence | postgres
(1 row)

查询元命令 \ds 的完整SQL。

SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('S','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;

可以看到 WHERE 条件中包含 pg_catalog.pg_table_is_visible(c.oid)，查询搜索路径参数：

postgis=# SHOW search_path;
      search_path       
------------------------
 "$user", public, tiger

搜索路径中不包含 Schema gis，使用 pg_table_is_visible 函数查询返回 f：

postgis=# select pg_catalog.pg_table_is_visible(20962);
 pg_table_is_visible 
---------------------
 f
(1 row)

postgis=# select pg_catalog.pg_table_is_visible('gis.gis_osm_railways_free_1_id_seq'::regclass);
 pg_table_is_visible 
---------------------
 f
(1 row)

# 补充一下，'gis.gis_osm_railways_free_1_id_seq'::regclass 就相当于 
SELECT oid FROM pg_class WHERE relname = 'gis_osm_railways_free_1_id_seq';

设置搜索路径包含 Schema gis，查询就正常了

postgis=# set search_path="$user", public, tiger, gis;
SET
postgis=# SHOW search_path;
         search_path         
-----------------------------
 "$user", public, tiger, gis
(1 row)

postgis=# \ds
                       List of relations
 Schema |              Name              |   Type   |  Owner   
--------+--------------------------------+----------+----------
 gis    | gis_osm_railways_free_1_id_seq | sequence | postgres
 public | us_gaz_id_seq                  | sequence | postgres
 public | us_lex_id_seq                  | sequence | postgres
 public | us_rules_id_seq                | sequence | postgres