PostgreSQL 從熊燦燦一個獲取固定字元的SQL 分析巧妙之處

某天群裡一個同學丟擲一個問題，關於獲取欄位型別中的設定的值，隨即熊老師在群裡丟擲以下的一個SQL  （秒拋）

SELECT
  CASE atttypid
         WHEN 21 /*int2*/ THEN 16
         WHEN 23 /*int4*/ THEN 32
         WHEN 20 /*int8*/ THEN 64
         WHEN 1700 /*numeric*/ THEN
              CASE WHEN atttypmod = -1
                   THEN null
                   ELSE ((atttypmod - 4) >> 16) & 65535    
                   END
         WHEN 700 /*float4*/ THEN 24 /*FLT_MANT_DIG*/
         WHEN 701 /*float8*/ THEN 53 /*DBL_MANT_DIG*/
         ELSE null
  END   AS numeric_precision,
  CASE
    WHEN atttypid IN (21, 23, 20) THEN 0
    WHEN atttypid IN (1700) THEN            
        CASE
            WHEN atttypmod = -1 THEN null       
            ELSE (atttypmod - 4) & 65535            
        END
       ELSE null
  END AS numeric_scale,
  *
FROM
    pg_attribute
 where attrelid = 'xcc.xcc'::regclass and attname = 'number_b';

這個問題實際是對欄位中的一種特殊的型別numberic 進行分析，並獲取其中欄位中的兩個數字，並進行展示，聽上去很簡單。

首先我們從兩個層面去分析這個SQL

1   結構層
這個SQL 主要展示的有兩個層面  1  numeric_scale  2 numeric_scale
在基於 numeric_scale 的部分 7 個大判斷，中包含1個子判斷
numeric_scale 3個判斷，包含1個子判斷

2   判斷邏輯
首先針對 atttypid 的值進行判斷，這裡需要說明的是 atttpid 的部分的值是需要搞清楚每個值代表的意義。

這裡就產生一個問題了，atttpid 的值 與實際的欄位的名字之間的對應關係，這裡我個人透過各種方式並未找到 值與欄位型別之間的對應關係表。

我個人能想到的方法就是建立一個包含大部分欄位型別然後透過這個表來查到欄位與atttypid 之間的關係。

所以寫這個SQL 的人必然是做過這個基礎性的工作，這實際上體現了撰寫這個SQL 的人對於技術的嚴謹和認真。

判斷邏輯中首先過濾了整形的資料型別部分，int2 int4 int8 等都會被顯示，而本次提出問題的 numeric 中的欄位進行判斷在  atttypmod

這裡對於實際的計算部分進行一個解釋
numeric(5,4) => 327688   0101 0000 0000 0000 1000

numeric(5,5) => 327689   0101 0000 0000 0000 1001

numeric(2,2) => 393222   0110 0000 0000 0000 0110

numeric(7,2) => 458758   0111 0000 0000 0000 0110

numeric(8,2) => 524294   1000 0000 0000 0000 0110

numeric(9,2) => 589830   1001 0000 0000 0000 0110

第一個位元組為 numeric (n,m) 的N， 最後一個位元組為 m+4，即precision為第一個位元組，scale為最後一個位元組-4

計算公式：

atttypmod=-1表示null

precision: ((atttypmod - 4) >> 16) & 65535

scale: (atttypmod - 4) & 65535

所以透過上面的計算公式可以解決這個同學的問題。

另外為什麼atttypmod 中為什麼要減 4 ，這裡我們我們透過一個實驗可以獲得，舉例我們產生一個欄位是varchar型別 ，這裡給的值是200， 那麼我們可以看下圖atttypmod 是裡面的值是204 ，那麼從這裡就可以瞭解到為什麼上面的一個解釋中要 m+4  ,而給出的SQL 中要進行一個 atttymod -4 的工作。

最後，透過這個SQL 實際上可以看出撰寫者的對系統表的深刻理解，以及對於一些深層次系統表中 數字計算的部分，比如將  atttypmod -4 後變為16進位制後與65535 進行位與運算。

實際上這一個SQL 可以研究的地方還不少，截止目前，我也覺得並未對一些更深的問題有更深的理解。

最後這裡順道將一個大家都在用的表欄位型別表格列印的語句貼上上，PG的系統表奧妙無窮。

select
c.relname as 表名,
a.attname as 列名,
(case
when a.attnotnull = true then true
else false end) as 非空,
(case
when (
select
count(pg_constraint.*)
from
pg_constraint
inner join pg_class on
pg_constraint.conrelid = pg_class.oid
inner join pg_attribute on
pg_attribute.attrelid = pg_class.oid
and pg_attribute.attnum = any(pg_constraint.conkey)
inner join pg_type on
pg_type.oid = pg_attribute.atttypid
where
pg_class.relname = c.relname
and pg_constraint.contype = 'p'
and pg_attribute.attname = a.attname) > 0 then true
else false end) as 主鍵,
concat_ws('', t.typname) as 欄位型別,
(case
when a.attlen > 0 then a.attlen
when t.typname='bit' then a.atttypmod
else a.atttypmod - 4 end) as 長度,
col.is_identity as 自增,
col.column_default as 預設值,
(select description from pg_description where objoid = a.attrelid
and objsubid = a.attnum) as 備註
from
pg_class c,
pg_attribute a ,
pg_type t,
information_schema.columns as col
where
a.attnum>0
and a.attrelid = c.oid
and a.atttypid = t.oid
and col.table_name=c.relname and col.column_name=a.attname
order by
c.relname desc,
a.attnum asc;