三探 Postgresql Catalog
-----

psql 有一個選項 ECHO_HIDDEN, 當我們登入時,加上 -E 選項,就會啟用.
會顯示出我們使用 meta-command 查詢時,psql 使用的 SQL Command.
裡面會有許多關於 pg_catalog 的使用方式,這是很豐富的學習資源.

| => psql -E -U miku -W

[miku]# \d
********* QUERY **********
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 'table' WHEN 'I' THEN '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 ('r','p','v','m','S','f','')
      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;
**************************

                   List of relations
+--------+---------------------+---------------+-------+
| Schema |        Name         |     Type      | Owner |
+--------+---------------------+---------------+-------+
| miku   | ithelp190822        | table         | miku  |
| miku   | ithelp190822_id_seq | sequence      | miku  |
| miku   | sample_arr          | table         | miku  |
| miku   | sample_arr_id_seq   | sequence      | miku  |
| miku   | t1                  | table         | miku  |
| miku   | wrap_ithelp190828   | foreign table | miku  |
| public | dual                | view          | miku  |
+--------+---------------------+---------------+-------+
(7 rows)

登入後也可以選擇使用 \set 方式來切換

[miku]# \set ECHO_HIDDEN off
[miku]# \d
                   List of relations
+--------+---------------------+---------------+-------+
| Schema |        Name         |     Type      | Owner |
+--------+---------------------+---------------+-------+
| miku   | ithelp190822        | table         | miku  |
| miku   | ithelp190822_id_seq | sequence      | miku  |
| miku   | sample_arr          | table         | miku  |
| miku   | sample_arr_id_seq   | sequence      | miku  |
| miku   | t1                  | table         | miku  |
| miku   | wrap_ithelp190828   | foreign table | miku  |
| public | dual                | view          | miku  |
+--------+---------------------+---------------+-------+
(7 rows)

設成 off ,這樣就不會顯示 SQL Command了.
還有 noexec 可以選擇,這樣只會顯示 SQL Command , 方便學習.

[miku]# \set ECHO_HIDDEN noexec
[miku]# \d
********* QUERY **********
SELECT n.nspname as "Schema",
  ....  --同上
ORDER BY 1,2;
**************************

也可以在 psqlrc 設定檔設定,視需要再做切換.

接下來探討在 Postgresql中的 OID (Object Identifiers),
在昨天的例子中,時常看到 oid.
oid 是使用 unsigned 4-Byte integer
同樣的,也是可以在 pg_catalog 中查到

select oid
     , oid::regtype
     , typname
     , typnamespace::regnamespace
     , typowner::regrole
     , typlen
     , typalign
  from pg_catalog.pg_type
 where oid = 'oid'::regtype; 

+-----+-----+---------+--------------+----------+--------+----------+
| oid | oid | typname | typnamespace | typowner | typlen | typalign |
+-----+-----+---------+--------------+----------+--------+----------+
|  26 | oid | oid     | pg_catalog   | bunko666 |      4 | i        |
+-----+-----+---------+--------------+----------+--------+----------+
(1 row)

typalign 代表使用 integer, 在大多數機器上是 int4.
typlen 代表長度是 4.

oid 的型態,請參考以下文件中的 Table 8.24. Object Identifier Types
https://www.postgresql.org/docs/current/datatype-oid.html

也可以搭配用以下 SQL Command 查詢

select t.oid
     , classoid::regclass
     , t.typname
     , description
  from pg_catalog.pg_type t
  join pg_catalog.pg_description d
    on (t.oid = d.objoid)
 where t.typname ~ '^reg'
    or t.typname = 'oid'
 order by 1;
+------+----------+---------------+-----------------------------------+
| oid  | classoid |    typname    |                description        |
+------+----------+---------------+-----------------------------------+
|   24 | pg_type  | regproc       | registered procedure              |
|   26 | pg_type  | oid    | object identifier(oid), maximum 4 billion|
| 2202 | pg_type  | regprocedure  | registered procedure (with args)  |
| 2203 | pg_type  | regoper       | registered operator               |
| 2204 | pg_type  | regoperator   | registered operator (with args)   |
| 2205 | pg_type  | regclass      | registered class                  |
| 2206 | pg_type  | regtype       | registered type                   |
| 3734 | pg_type  | regconfig   | registered text search configuration|
| 3769 | pg_type  | regdictionary | registered text search dictionary |
| 4089 | pg_type  | regnamespace  | registered namespace              |
| 4096 | pg_type  | regrole       | registered role                   |
+------+----------+---------------+-----------------------------------+
(11 rows)