PostgreSQL與Oracle在資料比較上存在差異,本節簡單介紹OPostgreSQL的資料型別轉換規則(第二部分).

### 一、函式
函式呼叫中依賴的指定函式使用以下過程確定.
**Function Type Resolution**
1.從pg_proc中選擇函式.通常情況下,如果不指定schema,則在當前搜尋路徑中選擇名稱&引數個數匹配的,否則將選擇指定schema的函式.
a.如在該搜尋路徑中存在多個相同引數型別的運算子,則選擇最早出現的那個.對於具有不同引數型別的運算子,無論搜尋路徑如何設定,都被認為是平等的.
b.如果函式使用VARIADIC陣列引數宣告,但呼叫沒有使用VARIADIC關鍵字,那麼陣列引數會被替換為陣列元素型別的一個或多個值以匹配函式呼叫.在這樣展開後,可能與NON-VARIADIC的函式有相同的引數,在這種情況下,使用搜尋路徑最終出現的那個,或者使用同一個schema中NON-VARIADIC的那個.
c.有預設引數值的函式會匹配任何省略了零個或多個預設引數位置的函式呼叫.如果有多個函式匹配,搜尋路徑中最終出現的那個會被選用.如果在同一個schema中存在兩個或以上這樣的函式,這時候PG無法選擇使用哪個,因此會報錯:"ambiguous function call".
2.檢查是否接受輸入的引數型別.如存在,則使用此函式.與運算子類似,會有安全上的漏洞.
3.如果沒有完全匹配的函式,檢查函式呼叫是否需要型別轉換.這會出現在函式呼叫只有一個引數並且函式名稱與內部函式名稱一樣.此外,函式引數必須是unknown-type literal,或者可binary-coercible為命名資料型別,或者透過I/O函式轉換為命名資料型別.如果滿足這些條件,那麼函式呼叫會被視為CAST的形式.
4.尋找最佳匹配
參照operator運算子的說明.

下面是一些例子:
*Round*
round函式在pg_proc中的定義如下:
```
testdb=# select oid,proname,provariadic,proargtypes,prorettype,prosrc from pg_proc where proname = 'round';
 oid  | proname | provariadic | proargtypes | prorettype |            prosrc             
------+---------+-------------+-------------+------------+-------------------------------
 1342 | round   |           0 | 701         |        701 | dround
 1707 | round   |           0 | 1700 23     |       1700 | numeric_round
 1708 | round   |           0 | 1700        |       1700 | select pg_catalog.round($1,0)
(3 rows)
```
其中proargtypes是引數型別,prorettype是返回型別,prosrc是函式實現"原始碼".
型別23/701/1700定義如下
```
testdb=# select oid,typname,typalign,typstorage from pg_type where oid in (23,701,1700);
 oid  | typname | typalign | typstorage 
------+---------+----------+------------
   23 | int4    | i        | p
  701 | float8  | d        | p
 1700 | numeric | i        | m
(3 rows)
```
執行SQL
```
testdb=# SELECT round(4, 4);
 round  
--------
 4.0000
(1 row)
```
在pg_proc中,只有一個函式(oid = 1707)有兩個引數,第一個引數型別視為numeric,第二個為integer,那麼第一個引數4會自動轉換為numeric型別,該SQL語句與"SELECT round(CAST (4 AS numeric), 4);"無異.

*Variadic*
首先定義一個函式variadic_example
```
testdb=# CREATE FUNCTION public.variadic_example(VARIADIC numeric[]) RETURNS int
testdb-#   LANGUAGE sql AS 'SELECT 1';
CREATE FUNCTION
```
函式定義
```
testdb=# select oid,proname,provariadic,proargtypes,prorettype,prosrc from pg_proc where proname = 'variadic_example';
  oid  |     proname      | provariadic | proargtypes | prorettype |  prosrc  
-------+------------------+-------------+-------------+------------+----------
 32787 | variadic_example |        1700 | 1231        |         23 | SELECT 1
(1 row)

testdb=# select oid,typname,typalign,typstorage from pg_type where oid in (1231,1700);
 oid  | typname  | typalign | typstorage 
------+----------+----------+------------
 1231 | _numeric | i        | x
 1700 | numeric  | i        | m
(2 rows)
```
執行函式,該函式接受可變引數關鍵字,但不需要指定,可允許整數和數值引數:
```
testdb=# SELECT public.variadic_example(0),
testdb-#        public.variadic_example(0.0),
testdb-#        public.variadic_example(VARIADIC array[0.0]);
 variadic_example | variadic_example | variadic_example 
------------------+------------------+------------------
                1 |                1 |                1
(1 row)
```
上述第一次和第二次呼叫將更傾向明確定義的函式:
```
testdb=# CREATE FUNCTION public.variadic_example(numeric) RETURNS int
  LANGUAGE sql AS 'SELECT 2';
testdb=# CREATE FUNCTION

testdb=# CREATE FUNCTION public.variadic_example(int) RETURNS int
  LANGUAGE sql AS 'SELECT 3';
testdb=# CREATE FUNCTION

testdb=# SELECT public.variadic_example(0),
testdb-#        public.variadic_example(0.0),
testdb-#        public.variadic_example(VARIADIC array[0.0]);
 variadic_example | variadic_example | variadic_example 
------------------+------------------+------------------
                3 |                2 |                1
(1 row)
```

*Substring*
有多個substr函式:
```
testdb=# select oid,proname,provariadic,proargtypes,prorettype,prosrc from pg_proc where proname = 'substr';
 oid  | proname | provariadic | proargtypes | prorettype |       prosrc        
------+---------+-------------+-------------+------------+---------------------
  877 | substr  |           0 | 25 23 23    |         25 | text_substr
  883 | substr  |           0 | 25 23       |         25 | text_substr_no_len
 2085 | substr  |           0 | 17 23 23    |         17 | bytea_substr
 2086 | substr  |           0 | 17 23       |         17 | bytea_substr_no_len
(4 rows)

testdb=# select oid,typname,typalign,typstorage from pg_type where oid in (17,23,25);
 oid | typname | typalign | typstorage 
-----+---------+----------+------------
  17 | bytea   | i        | x
  23 | int4    | i        | p
  25 | text    | i        | x
(3 rows)

```
如未指定引數型別呼叫函式,系統會優先選擇引數為text + int4的函式:
```
testdb=# SELECT substr('1234', 3);
 substr 
--------
 34
(1 row)
```
如指定型別為varchar,則轉換為text
```
testdb=# SELECT substr(varchar '1234', 3);
 substr 
--------
 34
(1 row)

testdb=# SELECT substr(CAST (varchar '1234' AS text), 3);
 substr 
--------
 34
(1 row)
```
如第一個引數為integer,系統無轉換函式,則會報錯
```
testdb=# SELECT substr(1234, 3);
psql: ERROR:  function substr(integer, integer) does not exist
LINE 1: SELECT substr(1234, 3);
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
testdb=# 
```

**Value Storage/UNION, CASE, and related constructs下一節再行介紹**

### 二、參考資料
[PostgreSQL Type Conversion](https://www.postgresql.org/docs/11/typeconv-func.html)

Oracle vs PostgreSQL，研發注意事項（10）- PostgreSQL資料型別轉換規則#2

相關文章