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