4.2. PostgreSQL值運算式

標籤：

4.2. 值運算式

值運算式用在各種文法環境中，比如在 SELECT 命令的目標列表中，在 INSERT 或 UPDATE 中用作新的列值，或者在許多命令的搜尋條件中使用。我們有時候把值運算式的結果叫做標量 ，以便與一個表運算式的結果相區別(是一個表)。因此值運算式也叫做標量運算式(或簡稱運算式)。運算式文法允許對來自基本部分的數值進行算術、邏輯、集合、和其它運算。

值運算式是下列內容之一：

• 一個常量或者字面值

• 一個欄位引用

• 一個位置參數引用(在函式宣告體中或預編寫的語句中)

• 一個下標運算式

• 一個欄位選擇運算式

• 一個操作符調用

• 一個函數調用

• 一個聚集運算式

• 一個類型轉換

• 一個標量子查詢

• 一個數組構造器

• 一個行構造器

• 一個在圓括弧裡面的值運算式(可用於子運算式分組和覆蓋優先順序)

除了這個列表以外，還有許多構造可以歸類為運算式，但是不遵循任何通用的文法規則。它們通常有函數或操作符的語義，並且在章9裡合適的位置描述。一個例子是 IS NULL 子句。

我們已經在節4.1.2裡有討論過的內容了。下面的節討論剩下的選項。

4.2.1. 欄位引用

一個欄位可以用下面的形式引用：

correlation.columnname

correlation 是一個表的名字(可能有模式修飾)，或者是用 FROM 子句這樣的方法定義的表的別名，或者是關鍵字 NEW 或 OLD (NEW 和 OLD 只能出現在一條改寫規則中，而其它相關的名字可以用於任意 SQL 陳述式中)。如果在當前查詢所使用的所有表中，該欄位名字是唯一的，那麼這個相關名字(correlation)和分隔用的點就可以省略(參見章7)。

4.2.2. 位置參數

位置參數引用用於標識從外部給 SQL 陳述式的參數。參數用於 SQL 函數定義語句和預編寫的查詢。有些用戶端庫還支援在 SQL 命令字串外邊聲明資料值，這種情況下參數用於引用 SQL 字串行外的資料。一個參數的形式如下：

$number

比如下面這個 dept 函數的定義

CREATE FUNCTION dept(text) RETURNS dept    AS $$ SELECT * FROM dept WHERE name = $1 $$    LANGUAGE SQL;

在函數被調用的時候這裡的 $1 將引用第一個參數。

4.2.3. 下標

如果一個運算式產生一個數群組類型的數值，那麼我們可以通過下面這樣的運算式來提取數組中的元素

expression[subscript]

如果是多個相鄰的元素("數組片斷")可以用下面的方法抽取([ ] 按照字面文本的方式出現)

expression[lower_subscript:upper_subscript]

每個 subscript 自己都是一個運算式，它必鬚生成一個整數值。

通常，數組 expression必須用圓括弧包圍，但如果只是一個欄位引用或者一個位置參數，那麼圓括弧可以省略。同樣，如果源數組是多維的，那麼多個下標可以串連在一起。比如：

mytable.arraycolumn[4]mytable.two_d_column[17][34]$1[10:42](arrayfunction(a,b))[42]

最後一個例子裡的圓括弧是必須的。參閱節8.10擷取有關數組的更多資訊。

4.2.4. 欄位選擇

如果一個運算式產生一個複合類型(行類型)，那麼用下面的方法可以抽取一個指定的欄位

expression.fieldname

通常，行 expression 必須用圓括弧包圍，但是如果要選取的運算式只是一個表引用或者位置參數，可以省略圓括弧。比如

mytable.mycolumn$1.somecolumn(rowfunction(a,b)).col3

因此，一個全稱的欄位引用實際上只是一個欄位選擇文法的特例。

4.2.5. 操作符調用

操作符調用有三種文法：

expression operator expression (雙目中綴操作符)

operator expression (單目首碼操作符)

expression operator (單目尾碼操作符)

這裡的 operator 記號遵循節4.1.3的文法規則，或者是記號 AND, OR, NOT 之一。或者是一個被修飾的操作符名

OPERATOR(schema.operatorname)

具體存在哪個操作符以及它們是單目還是雙目取決於系統或使用者定義了什麼操作符。章9描述了內建的操作符。以及http://www.infocool.net可參考相關資料。

4.2.6. 函數調用

函數調用的文法是合法函數名(可能有模式名修飾)後面跟著包含參數列表的圓括弧：

function ([expression [, expression ...]] )

比如，下面的代碼計算 2 的平方根：

sqrt(2)

內建函數的列表在章9裡。其它函數可由使用者添加。

4.2.7. 聚集運算式

一個聚集運算式代表一個聚集合函式對查詢選出的行的處理。一個聚集合函式把多個輸入縮減為一個輸出值，比如給輸入求和或求平均。一個聚集運算式的文法是下列之一：

aggregate_name (expression [ , ... ] )aggregate_name (ALL expression [ , ... ] )aggregate_name (DISTINCT expression [ , ... ] )aggregate_name ( * )

這裡的 aggregate_name 是前面定義的聚集(可能是帶有模式的全稱)，而 expression 是一個本身不包含聚集運算式的任意值運算式。

第一種形式的聚集運算式為所有非 NULL 的輸入行調用聚集(實際上，是否忽略 NULL 由聚集合函式決定，但是所有標準的聚集合函式都忽略它們)。第二種形式與第一種等價(因為 ALL 是預設值)。第三種形式為所有輸入行中所有唯一的非 NULL 值調用聚集。最後一種形式為每個輸入行(不管是否為 NULL)調用一次聚集；因為沒有聲明特定的輸入值。通常它只用於 count(*) 聚集合函式。

比如，count(*) 產生輸入行的總數；count(f1) 產生 f1 不為 NULL 的輸入行數；count(distinct f1) 產生 f1 唯一且非 NULL 的行數。

預定義的聚集合函式在節9.15裡描述。其它聚集合函式可以由使用者增加。

一個聚集運算式只能在SELECT 命令的結果清單或者 HAVING 子句裡出現。禁止在其它子句裡出現(比如 WHERE 子句)，因為這些子句邏輯上在產生聚集結果之前計算。

如果一個聚集運算式出現在一個子查詢裡(參閱節4.2.9和節9.16)，聚集通常是在子查詢中進行計算。但是如果聚集的參數只包含外層查詢的變數則例外：這個聚集會屬於離他最近的外層查詢，並且在該查詢上進行計算。該聚集運算式整體上屬於它出現的子查詢對外層查詢的引用，其作用相當於子查詢每一次計算中的一個常量。前述限制(聚集運算式只能出現在結果列或者 HAVING 子句中)只適用於聚集所屬的查詢層。

【注意】PostgreSQL 目前並不支援帶有多個輸入運算式的 DISTINCT 。

4.2.8. 類型轉換

一個類型轉換聲明一個從一種資料類型到另外一種資料類型的轉換。PostgreSQL 接受兩種等效的類型轉換文法：

CAST ( expression AS type )expression::type

CAST 文法遵循 SQL 標準；:: 文法是 PostgreSQL 曆史用法。

如果對一個已知類型的值運算式應用轉換，它代表一個運行時類型轉換。只有在已經定義了合適的類型轉換操作的情況下，該轉換才能成功。請注意這一點和用於常量的轉換略有區別(如節4.1.2.5所示)。一個應用於字串文本的轉換表示給該字串文本的值賦予一個初始類型，因此它對於任何類型都會成功(如果字串文本的內容符合該資料類型的輸入文法)。

如果一個值運算式的值對某類型而言不存在混淆的情況，那麼我們可以省略明確的類型轉換(比如，在給一個表欄位賦值的時候)，而由系統自動執行類型轉換。不過，自動轉換隻適用於那些系統資料表中標記著"OK to apply implicitly"的轉換函式。其它轉換函式必須用明確的轉換文法調用。這些限制是為了避免一些怪異的轉換被自動的應用。

我們也可以用函數風格的文法聲明一個類型轉換：

typename ( expression )

不過，這個方法只能用於那些類型名同時也是有效函數名的類型。比如，double precision 就不能這麼用，但是等效的 float8 可以。同樣，interval, time, timestamp 如果加了雙引號也只能這麼用，因為存在文法衝突。因此，函數風格的類型轉換會導致不一致，所以應該避免這麼使用。函數樣文法實際上就是一個函數調用。如果使用兩種標準轉換文法做運行時轉換，那麼它將在內部調用一個登入的函數執行轉換。通常，這種轉換函式和它們的輸出類型同名，但是可以移植的程式不能依賴這一點。

4.2.9. 標量子查詢

一個標量子查詢是一個放在圓括弧裡只返回一行一列的普通 SELECT 查詢(參閱章7擷取有關書寫查詢的資訊)。該 SELECT 將被執行，而其傳回值將在周圍的值運算式中使用。把一個返回超過一行或者超過一列的查詢用做標量查詢是錯誤的。不過，子查詢不返回行則不算錯誤(標量結果被認為是 NULL)。子查詢可以引用外圍查詢的變數，這些變數在每次子查詢中當做常量使用。參見節9.16 以及http://www.infocool.net以擷取其它包含子查詢的運算式。

比如，下面的查詢找出每個州中的最大人口數量的城市：

SELECT name, (SELECT max(pop) FROM cities WHERE cities.state = states.name)    FROM states;

4.2.10. 數組構造器

一個數組構造器是一個運算式，它從自身成員元素上構造一個數組值。一個簡單的數組構造器由關鍵字 ARRAY 、一個左方括弧 [ 、一個或多個表示數組元素值的運算式(用逗號分隔)、一個右方括弧 ] 組成。比如

SELECT ARRAY[1,2,3+4];  array--------- {1,2,7}(1 row)

數組元素類型是成員運算式的公用類型，使用和 UNION 或 CASE構造一樣的規則決定(參閱節10.5)。

多維陣列值可以通過嵌套數組構造器的方法來製作。內層構造器中的 ARRAY 關鍵字可以省略。比如，下面的兩句產生同樣的結果：

SELECT ARRAY[ARRAY[1,2], ARRAY[3,4]];     array--------------- {{1,2},{3,4}}(1 row)SELECT ARRAY[[1,2],[3,4]];     array--------------- {{1,2},{3,4}}(1 row)

因為多維陣列必須是方形，所以同層的內層構造器必鬚生成同維的子數組。

多維陣列構造器元素可以是任何產生合適數組的東西，而不僅僅是一個子 ARRAY 構造。比如：

CREATE TABLE arr(f1 int[], f2 int[]);INSERT INTO arr VALUES (ARRAY[[1,2],[3,4]], ARRAY[[5,6],[7,8]]);SELECT ARRAY[f1, f2, ‘{{9,10},{11,12}}‘::int[]] FROM arr;                     array------------------------------------------------ {{{1,2},{3,4}},{{5,6},{7,8}},{{9,10},{11,12}}}(1 row)

我們也可以從一個子查詢的結果中構造一個數組。此時，數組構造器是關鍵字 ARRAY 後跟著一個用圓括弧(不是方括弧)包圍的子查詢。比如：

SELECT ARRAY(SELECT oid FROM pg_proc WHERE proname LIKE ‘bytea%‘);                          ?column?------------------------------------------------------------- {2011,1954,1948,1952,1951,1244,1950,2005,1949,1953,2006,31}(1 row)

子查詢必須只返回一個單獨的欄位。產生的一維數組將為子查詢裡每行結果產生一個元素，元素類型匹配子查詢的輸出欄位。

用 ARRAY 建立的數組下標總是從壹開始。有關數組的更多資訊，參閱節8.10。

4.2.11. 行構造器

行構造器是一個從提供給它的成員欄位數值中構造行值(也叫複合類型值)的運算式。一個行構造器由關鍵字 ROW 、一個左圓括弧、零個或多個作為列欄位值的運算式(用逗號分隔)、一個右圓括弧組成。比如：

SELECT ROW(1,2.5,‘this is a test‘);

如果在列表裡有多個運算式，那麼關鍵字 ROW 是可選的。

行構造器可以包含 rowvalue.* 文法，它將被擴充為行值元素的列表，就像將 .* 文法用於一個 SELECT 列表頂層一樣。例如，如果表 t 有 f1 和 f2 兩個欄位，那麼下面兩句是等價的：

SELECT ROW(t.*, 42) FROM t;SELECT ROW(t.f1, t.f2, 42) FROM t;

【注意】在 PostgreSQL 8.2之前，.* 文法是不會被擴充的，所以 ROW(t.*, 42) 將建立一個兩欄位的行，其第一個欄位是另一行的值。新的行為通常更有用。如果你需要舊式的嵌套行值的做法，請將內部的行值寫成不包含 .* ，比如 ROW(t, 42) 。

預設時，ROW 運算式建立的值是一個匿名的記錄類型。如果必要，你可以把它轉換成一個命名的複合類型(既可以是一個表的行類型，也可以是一個用 CREATE TYPE AS 建立的複合類型)。可能會需要一個明確的轉換以避免歧義。比如：

CREATE TABLE mytable(f1 int, f2 float, f3 text);CREATE FUNCTION getf1(mytable) RETURNS int AS ‘SELECT $1.f1‘ LANGUAGE SQL;-- 因為只有一個getf1()存在，所以不需要類型轉換SELECT getf1(ROW(1,2.5,‘this is a test‘)); getf1-------     1(1 row)CREATE TYPE myrowtype AS (f1 int, f2 text, f3 numeric);CREATE FUNCTION getf1(myrowtype) RETURNS int AS ‘SELECT $1.f1‘ LANGUAGE SQL;-- 現在我們需要類型轉換以表明調用哪個函數：SELECT getf1(ROW(1,2.5,‘this is a test‘));ERROR:  function getf1(record) is not uniqueSELECT getf1(ROW(1,2.5,‘this is a test‘)::mytable); getf1-------     1(1 row)SELECT getf1(CAST(ROW(11,‘this is a test‘,2.5) AS myrowtype)); getf1-------    11(1 row)

行構造器可以用於製作儲存在複合類型欄位中的複合類型值，或者是傳遞給一個接受複合型別參數的函數。另外，我們也可以用它比較兩個行值或者用 IS NULL 或 IS NOT NULL 測試一個行值，比如：

SELECT ROW(1,2.5,‘this is a test‘) = ROW(1, 3, ‘not the same‘);SELECT ROW(table.*) IS NULL FROM table;  -- detect all-null rows

更多的細節，請參閱節9.17。行構造器還可以用於串連子查詢，這些在節9.16裡面有詳細討論。

4.2.12. 運算式計算規則

子運算式的計算順序是沒有定義的。特別要指出的是，一個操作符或者函數的輸入並不一定是按照從左向右的順序或者以某種特定的順序進行計算的。

另外，如果一個運算式的結果可以通過只判斷它的一部分就可以得到，那麼其它子運算式就可以完全不計算了。比如，如果我們這麼寫

SELECT true OR somefunc();

那麼 somefunc() 就(可能)根本不會被調用。即使像下面這樣寫也是一樣

SELECT somefunc() OR true;

請注意這和某些程式設計語言裡從左向右"短路"是不一樣的。

因此，拿有副作用的函數作為複雜運算式的一部分是不明智的。在 WHERE 和 HAVING 子句裡依賴副作用或者是計算順序是特別危險的，因為這些子句都是作為產生一個執行規劃的一部分進行了大量的再處理。在這些子句裡的布林運算式(AND/OR/NOT 的組合)可以用布爾代數運算律允許的任何方式進行識別。

如果需要強制計算順序，那麼可以使用 CASE 構造(參閱節9.13)。比如，下面是一種企圖避免在 WHERE 子句裡被零除的不可靠方法：

SELECT ... WHERE x <> 0 AND y/x > 1.5;

更多資訊參考http://www.infocool.net/PostgreSQL/index.htm

但是下面這個是安全的：

SELECT ... WHERE CASE WHEN x <> 0 THEN y/x > 1.5 ELSE false END;

這種風格的 CASE 構造會阻止最佳化，因此應該只在必要的時候才使用。在這個特殊的例子裡，毫無疑問寫成 y > 1.5*x 更好。

4.2. PostgreSQL值運算式