PostgreSQL7.0手冊-用戶手冊-6. 類型轉換
2019-09-08 23:32:53
供稿：網友
第六章. 類型轉換
內容
概述
操作符
函數
查詢目標
UNION 查詢
SQL 查詢可能（有意無意地）要求在同一表達式里混合不同的數據類型．Postgres 在計算混合類型表達式方面有許多擴展性很強的功能。
在大多數情況下，用戶不需要明白類型轉換機制的細節．但是，由 Postgres 所進行的隱含的類型轉換會對查詢的結果產生明顯的影響，這些影響又可以由用戶或程序員通過顯式的類型轉換進行剪裁利用．
本章介紹 Postgres 類型轉換的傳統和機制。關于特定的類型和函數及操作符的進一步信息，請參考用戶手冊和程序員手冊有關章節．
在程序員手冊里有關于隱含類型轉換的具體算法的更詳細的介紹．
概述
SQL 是強類型語言。也就是說，每一數據都與一個決定其特性和用法的數據類型相聯。Postgres有一個可擴展的數據類型系統，該系統比其他RDBMS 對應系統的實現更具通用性和靈活性。因而，Postgres大多數類型轉換的特性應該是由通用規則來管理的，而不是由專門搜索方法來分析混合類型表達式的含義，即便是用戶定義的類型也應該如此。
Postgres 掃描/分析器只將語法元素分解成五個基本種類：整數（integers），浮點數（floats），字符串（strings），名字（names）和關鍵字（keywords）．大多數擴展的類型首先表征為字符串（strings）．SQL 語言的定義允許將類型名聲明為字符串，這個機制被Postgres 用于令分析器沿著正確的方向運行．例如，下面查詢
tgl=> SELECT text 'Origin' AS "Label", point '(0,0)' AS "Value";
Label | Value
--------+-------
Origin | (0,0)
(1 row)
有兩個字符串（strings）類型 text 和 point．如果類型沒有聲明，（該類型）先被初始化成一個擁有存儲空間的unknown（未知）類型，該類型將在后面描述的晚期階段分析．
在 Postgres 分析器里，有四種基本的 SQL 元素需要獨立的類型轉換規則：
操作符
Postgres 允許表達式里使用雙目操作符（兩個參數），也允許使用左目或右目操作符（單目操作符，一個參數）．
函數調用
多數 Postgres 類型系統是建筑在一套豐富的函數上的．任何查詢中調用的函數的參數，不管是一個還是多個，必須符合該函數在系統表里的定義．
查詢目標
SQL INSERT 語句將查詢結果插入表中．查詢表達式的類型必須和insert語句的目標列一致或者是（可能需要）轉換成一致的．
聯合(UNION)查詢
因為 UNION SELECT 語句的所有查詢結果必須在一列里顯示出來，所以每個 SELECT 子句中的元素類型必須相互匹配并轉換成一套統一類型．
許多通用的類型轉換規則使用建立在 Postgres 函數和操作符系統表基礎上的簡單轉換來實現．在轉換規則中包括了一些啟發方法，以便更好的支持SQL92 標準中一些傳統的內部類型，像 smallint，integer，和float．
Postgres 分析器的習慣是：所有類型轉換函數接受一個類型是源類型的參數，該參數與目標類型同名．任何符合這一標準的函數都被認為是一個有效的轉換函數，因而可以被分析器當作轉換函數使用．這個簡單的假設令分析器在不需要寫硬代碼的基礎上就可以充分利用類型轉換的能力，也讓用戶定義的類型可以透明地使用同一特性．
分析器中還有一個搜索器用于更好地猜測 SQL 標準類型的確切特性．分析器里有五種定義的類型：布爾（boolean），字符串（string），數字（numeric），幾何（geometric）和用戶定義（user-defined）．除用戶定義類型外，每種類型都有一種"優選類型"用于解決類型定義不明確的問題．每一個"用戶定義"的類型就是其自身的"優選類型"，所以那些含混不清的表達式（在分析結果中有多種可能的表達式）如果只有一個用戶定義類型則可以正確分析，而那些有多個用戶定義類型的表達式會仍然是含糊的，因而會拋出分析錯．
含糊的表達式如果其多種候選可能性都是同一種數據類型，則有可能被正確分析，而如果其候選可能性是幾種數據類型，則分析很可能會報錯，并要求用戶將表達式明確化．
要決
所有類型轉換規則都是建立在下面幾個基本原則上的：
隱含轉換決不能有奇怪的或不可預見的輸出．
用戶定義類型，因為分析器對其沒有預先的認識，在類型級別中應該級別較"高"．在混合類型的表達式里，內部類型總是應該轉換成用戶定義類型．（當然只是在必須轉換的時候）．
用戶定義類型是不相關的．當前，Postgres 除了用于內部數據類型的硬代碼搜索器和以系統表中現有函數為基礎的隱含類型關系外，沒有任何可用于處理類型間關系的信息?！?
如果一個查詢不需要隱含的類型轉換，分析器或執行器不應該進行更多的額外操作．這就是說，任何一個類型匹配，格式清晰的查詢不應該在分析器里耗費更多的時間，也不應該做任何不必要的類型轉換．
另外，如果一個查詢通常使用某個函數進行隱含類型轉換，而用戶顯式定義了一個有正確參數的函數，解釋器應該使用新函數取代原先舊函數的隱含操作。
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
操作符
轉換過程
操作符計算
在 pg_operator 系統表里找出準確的匹配．
如果一個雙目操作符的一個參數是 unknown．則假設其與另一個參數類型相同．
掉轉參數，尋找一個做為反向操作符匹配時指向自身操作符．如果找到，則掉轉分析樹上的參數并使用該操作符。
尋找最優匹配
生成一個同名的操作符的列表?！?
如果表中只有一個操作符：如果輸入類型可以匹配或轉換，則使用該操作符，否則輸出一個錯誤?！?
挑出具有最多顯式的類型匹配的操作符．挑出所有沒有顯式的類型匹配的操作符并進行下一步．如果結果只有一個候選操作符，而且類型匹配或可轉換．則使用之．
如果輸入參數是 "unknown"， 將輸入按布爾（boolean），數字（numeric），字符串（string），幾何（geometric）或用戶定義（user-defined）分類．如果分類結果有多種類型，或超過一種用戶定義類型，則產生一個錯誤，因為在沒有更多線索的條件下不能導出正確的選擇．如果分類結果只有一類，則將先前"unknown"的類型轉為"優選類型"．
挑出類型匹配最準確的和從前一步中挑出的匹配各類的"優選類型"的操作符．如果還有超過一個的候選操作符或是沒有候選操作符，則產生一個錯誤．
例子
指數操作符
在分類里只有一個指數操作符，它以 float8 作為參數．掃描器給下面查詢表達式的兩個參數賦予int4 的初始類型：
tgl=> select 2 ^ 3 AS "Exp";
Exp
-----
8
(1 row)
分析器對兩個參數都做類型轉換，查詢等效于：
tgl=> select float8(2) ^ float8(3) AS "Exp";
Exp
-----
8
(1 row)
或
tgl=> select 2.0 ^ 3.0 AS "Exp";
Exp
-----
8
(1 row)
注意：最后的形式最高效，因為不用調用函數做隱含類型轉換．這對小查詢沒有什么影響，但可能對那些操作大表的查詢的性能產生較大影響．
字符串連接
一種類字符串的語法既可以用于字符串也可以用于復雜的擴展類型．包含不明類型的字串使用可能的候選操作符匹配．
有一個未聲明的參數：
tgl=> SELECT text 'abc' || 'def' AS "Text and Unknown";
Text and Unknown
------------------
abcdef
(1 row)
本例中分析器尋找一個兩個參數都是 text 的操作符．因為有一個這樣的操作符，它認為另一個參數的類型是text．
聯接未聲明類型：
tgl=> SELECT 'abc' || 'def' AS "Unspecified";
Unspecified
-------------
abcdef
(1 row)
本例中對類型的初始值沒有任何暗示，因為查詢中沒有聲明任何類型．因此，分析器查找所有參數都是字符串類的候選操作符．并且它選擇"優選類型"，text，作為本查詢字符串類的類型．
注意: 如果用戶定義了一個新的數據類型，并且定義了用于該類型的操作符“||”，那么本查詢將不會象上面寫的那樣成功完成．這時分析器會因為現在有兩類候選操作符而無法決定使用哪個．
階乘
本例演示了一個有趣的結果．一般來說，階乘只用于整數，Postgres 操作符類別中用于階乘的只有一個，其以整數為操作數．如果輸入一個非整數數字參數．Postgres 將試圖把該參數轉換成整數進行階乘運算．
tgl=> select (4.3 !);
?column?
----------
24
(1 row)
注意: 這樣做當然會導致一個數學上有疑問的結果，因為非整數的階乘原則沒有定義．但是，數據庫的角色不是數學教學，而是數據操作．如果用戶一定要進行浮點數的階乘，Postgres 將盡可能服從．
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
函數
函數計算
在 pg_proc 系統表里查找準確的匹配．
查找最優的匹配
生成一個具有相同數量參數的同名函數的列表．
如果表中只有一個函數，而且該函數的輸入類型可以匹配或轉換則使用之，如果不能，生成一個錯誤．
找出所有具有最多顯式參數類型匹配的函數．如果沒有顯式參數匹配的函數則進行下一步．如果只有一個候選函數，而且其參數類型可以匹配或轉換，使用之．
如果任何輸入參數類型是"未知"（"unknown"）， 將輸入的參數分類成布爾（ boolean），數字（numeric）， 字符串（string）， 幾何（geometric）和用戶定義（user-defined）類型．如果分類后有多于一類，或多于一種用戶定義類型則生成一個錯誤，因為在沒有更多線索的情況下不能導出正確的選擇．如果分類結果只有一類，則將原先的"未知"（"unknown"）類型轉換成該類的"優選類型"?。?preferred type"）．
挑出類型匹配最準確的函數以及在上一步里每一類中匹配該類的"優選類型"（"preferred type"）的函數．如果這時仍然有多個函數可選，或一個都沒有，則生成一個錯誤．
例子
階乘函數
在 pg_proc 系統表里只定義了一個階乘函數．所以下面的查詢自動將 int2 參數轉換成 int4：
tgl=> select int4fac(int2 '4');
int4fac
---------
24
(1 row)
實際上它被分析器轉換成：
tgl=> select int4fac(int4(int2 '4'));
int4fac
---------
24
(1 row)
子字串函數
在 pg_proc 里定義了兩個 substr 函數．但是，其中只有一個使用兩個參數，參數類型分別是text 和 int4．
如果其中一個字符串常量的類型不明確，其類型直接與唯一的候選函數匹配：
tgl=> select substr('1234', 3);
substr
--------
34
(1 row)
如果該字符串定義為類型 varchar，就像大多數從表中取來的數據一樣，分析器將試著將其轉換成text：
tgl=> select substr(varchar '1234', 3);
substr
--------
34
(1 row)
被分析器轉換后變成：
tgl=> select substr(text(varchar '1234'), 3);
substr
--------
34
(1 row)
注意：分析器中有一些搜索器用于優化 char，varchar，和 text 類型之間的關系．這時，substr 將直接用 varchar 字符串調用，而不是在其中插入一個顯式的類型轉換調用．
而且，如果以 int4 為參數調用函數，分析器將試圖將其轉換成 text：
tgl=> select substr(1234, 3);
substr
--------
34
(1 row)
實際上是這樣執行的
tgl=> select substr(text(1234), 3);
substr
--------
34
(1 row)
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
查詢目標
目標計算
查找與目標準確的匹配．
如果必要的話，試著將表達式直接轉換成目標類型．
如果目標是定長類型（如 char 或定義了長度的 varchar）則試著找出一個有兩個參數的同名定長函數，該函數第一個參數類型為名稱，第二個參數類型是整形長度．
例子
varchar 存儲
對一個目標列定義為 varchar(4) 的查詢，下面查詢確保目標的正確定長：
tgl=> CREATE TABLE vv (v varchar(4));
CREATE
tgl=> INSERT INTO vv SELECT 'abc' || 'def';
INSERT 392905 1
tgl=> SELECT * FROM vv;
v
------
abcd
(1 row)
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
UNION?。摵希┎樵?
UNION 元素有些特別，因為它必須匹配一些也許不太類似的類型以生成一個唯一的結果集．
UNION 計算
檢測標明的類型，獲得所有結果．
使用UNION語句轉換所有結果，使之與第一個 SELECT 語句或目標列匹配．
例子
待定類型
tgl=> SELECT text 'a' AS "Text" UNION SELECT 'b';
Text
------
a
b
(2 rows)
簡單的 UNION
tgl=> SELECT 1.2 AS "Float8" UNION SELECT 1;
Float8
--------
1
1.2
(2 rows)
轉換的 UNION
union 的類型將被強制與union的第一個/頂端的語句的類型相同．
tgl=> SELECT 1 AS "All integers"
tgl-> UNION SELECT '2.2'::float4
tgl-> UNION SELECT 3.3;
All integers
--------------
1
2
3
(3 rows)
一個可選的分析器策略是從一組數據中選擇"最好"的類型，但這卻難以在分析器優良的遞歸技術中實現．不過，"最好"的類型在使用 into 向表中插入數據時使用：
tgl=> CREATE TABLE ff (f float);
CREATE
tgl=> INSERT INTO ff
tgl-> SELECT 1
tgl-> UNION SELECT '2.2'::float4
tgl-> UNION SELECT 3.3;
INSERT 0 3
tgl=> SELECT f AS "Floating point" from ff;
Floating point
------------------
1
2.20000004768372
3.3
(3 rows)
--------------------------------------------------------------------------------
