本節簡單介紹了PostgreSQL中的型別轉換實現.
首先來看看幾個示例SQL:
```
testdb=# 
testdb=# drop table if exists t_conv;
om t_conv where id = '1'::text;

explain verbose select * from t_conv where id > '13'::text;
DROP TABLE
testdb=# create table t_conv(id int);
CREATE TABLE
testdb=# 
testdb=# insert into t_conv values(1);
INSERT 0 1
testdb=# insert into t_conv values(2);
INSERT 0 1
testdb=# 
```
查詢條件為id = '1','1'轉換為int進行比較.
```
testdb=# explain verbose select * from t_conv where id = '1';
                          QUERY PLAN                           
---------------------------------------------------------------
 Seq Scan on public.t_conv  (cost=0.00..41.88 rows=13 width=4)
   Output: id
   Filter: (t_conv.id = 1)
(3 rows)
```
查詢條件為id = '1.1',出錯(1.1無法轉換為整型)
```
testdb=# explain verbose select * from t_conv where id = '1.1';
psql: ERROR:  invalid input syntax for type integer: "1.1"
LINE 1: explain verbose select * from t_conv where id = '1.1';
                                                        ^
```
查詢條件為id = '1'::text,出錯(text和int4沒有轉換規則)
```
testdb=# explain verbose select * from t_conv where id = '1'::text;
psql: ERROR:  operator does not exist: integer = text
LINE 1: explain verbose select * from t_conv where id = '1'::text;
                                                      ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
testdb=# 
```
查詢條件為id > '13'::text,轉換為numeric進行比較
```
testdb=# explain verbose select * from t_conv where id > '13'::text;
                           QUERY PLAN                           
----------------------------------------------------------------
 Seq Scan on public.t_conv  (cost=0.00..48.25 rows=850 width=4)
   Output: id
   Filter: ((t_conv.id)::numeric > '13'::numeric)
(3 rows)

testdb=# 
```
系統轉換規則定義,">"運算子有定義text&int的比較,而"="運算子沒有定義,因此"select * from t_conv where id = '1'::text"語句會出錯.
```
testdb=# select oid,typname from pg_type where typname in ('int4','text');
 oid | typname 
-----+---------
  23 | int4
  25 | text
(2 rows)

testdb=# select oid,oprname,oprleft,oprright,oprresult,oprcode from pg_operator where oprname = '=' and oprleft = 23 and oprright = 25;
 oid | oprname | oprleft | oprright | oprresult | oprcode 
-----+---------+---------+----------+-----------+---------
(0 rows)

```

總結一下:
**解析表示式,涉及不同資料型別時:
1.如有相應型別的Operator定義(pg_operator),則嘗試進行型別轉換,否則報錯;
2.如有相應型別的轉換規則,轉換為目標型別後解析,否則報錯.**

### 一、資料結構
**Form_pg_operator**
pg_operator中的定義,程式碼會其中的定義轉換為FormData_pg_operator結構體
```

/* ----------------
 *    pg_operator definition.  cpp turns this into
 *    typedef struct FormData_pg_operator
 * ----------------
 */
CATALOG(pg_operator,2617,OperatorRelationId)
{
  Oid     oid;      /* oid */

/* name of operator */
  NameData  oprname;

/* OID of namespace containing this oper */
  Oid     oprnamespace BKI_DEFAULT(PGNSP);

/* operator owner */
  Oid     oprowner BKI_DEFAULT(PGUID);

/* 'l', 'r', or 'b' */
  char    oprkind BKI_DEFAULT(b);

/* can be used in merge join? */
  bool    oprcanmerge BKI_DEFAULT(f);

/* can be used in hash join? */
  bool    oprcanhash BKI_DEFAULT(f);

/* left arg type, or 0 if 'l' oprkind */
  Oid     oprleft BKI_LOOKUP(pg_type);

/* right arg type, or 0 if 'r' oprkind */
  Oid     oprright BKI_LOOKUP(pg_type);

/* result datatype */
  Oid     oprresult BKI_LOOKUP(pg_type);

/* OID of commutator oper, or 0 if none */
  Oid     oprcom BKI_DEFAULT(0) BKI_LOOKUP(pg_operator);

/* OID of negator oper, or 0 if none */
  Oid     oprnegate BKI_DEFAULT(0) BKI_LOOKUP(pg_operator);

/* OID of underlying function */
  regproc   oprcode BKI_LOOKUP(pg_proc);

/* OID of restriction estimator, or 0 */
  regproc   oprrest BKI_DEFAULT(-) BKI_LOOKUP(pg_proc);

/* OID of join estimator, or 0 */
  regproc   oprjoin BKI_DEFAULT(-) BKI_LOOKUP(pg_proc);
} FormData_pg_operator;

/* ----------------
 *    Form_pg_operator corresponds to a pointer to a tuple with
 *    the format of pg_operator relation.
 * ----------------
 */
typedef FormData_pg_operator *Form_pg_operator;

```

### 二、原始碼解讀
**make_op**
變換運算子表示式以確保型別相容,會存在某些型別轉換.
```

/*
 * make_op()
 *    Operator expression construction.
 *
 * Transform operator expression ensuring type compatibility.
 * This is where some type conversion happens.
 * 變換運算子表示式以確保型別相容,會存在某些型別轉換.
 *
 * last_srf should be a copy of pstate->p_last_srf from just before we
 * started transforming the operator's arguments; this is used for nested-SRF
 * detection.  If the caller will throw an error anyway for a set-returning
 * expression, it's okay to cheat and just pass pstate->p_last_srf.
 * last_srf應為pstate->p_last_srf的一個複製.
 */
Expr *
make_op(ParseState *pstate, List *opname, Node *ltree, Node *rtree,
    Node *last_srf, int location)
{
  Oid     ltypeId,//左運算元型別OID
        rtypeId;//右運算元型別OID
  Operator  tup;//運算子tuple
  Form_pg_operator opform;//運算子form(參照pg_operator)
  Oid     actual_arg_types[2];//實際引數型別
  Oid     declared_arg_types[2];//運算子宣告的引數型別
  int     nargs;//引數格式
  List     *args;//引數連結串列
  Oid     rettype;//返回結果型別
  OpExpr     *result;//結果

/* Select the operator */
  //選擇運算子
  if (rtree == NULL)
  {
    /* right operator */
    //右樹為NULL,則為右運算子,如階乘運算子"!" : 10!
    ltypeId = exprType(ltree);
    rtypeId = InvalidOid;
    tup = right_oper(pstate, opname, ltypeId, false, location);
  }
  else if (ltree == NULL)
  {
    /* left operator */
    //左樹為空,則為左運算子,如按位NOT運算子 : "~21"
    rtypeId = exprType(rtree);
    ltypeId = InvalidOid;
    tup = left_oper(pstate, opname, rtypeId, false, location);
  }
  else
  {
    /* otherwise, binary operator */
    //二元運算子
    ltypeId = exprType(ltree);
    rtypeId = exprType(rtree);
    tup = oper(pstate, opname, ltypeId, rtypeId, false, location);
  }
  //獲取運算子
  opform = (Form_pg_operator) GETSTRUCT(tup);

/* Check it's not a shell */
  if (!RegProcedureIsValid(opform->oprcode))
    ereport(ERROR,
        (errcode(ERRCODE_UNDEFINED_FUNCTION),
         errmsg("operator is only a shell: %s",
            op_signature_string(opname,
                      opform->oprkind,
                      opform->oprleft,
                      opform->oprright)),
         parser_errposition(pstate, location)));

/* Do typecasting and build the expression tree */
  //執行型別轉換並構建表示式樹
  if (rtree == NULL)
  {
    /* right operator */
    args = list_make1(ltree);//引數
    actual_arg_types[0] = ltypeId;//左實際引數型別
    declared_arg_types[0] = opform->oprleft;//宣告型別
    nargs = 1;//引數個數
  }
  else if (ltree == NULL)
  {
    /* left operator */
    args = list_make1(rtree);
    actual_arg_types[0] = rtypeId;
    declared_arg_types[0] = opform->oprright;
    nargs = 1;
  }
  else
  {
    /* otherwise, binary operator */
    args = list_make2(ltree, rtree);
    actual_arg_types[0] = ltypeId;
    actual_arg_types[1] = rtypeId;
    declared_arg_types[0] = opform->oprleft;
    declared_arg_types[1] = opform->oprright;
    nargs = 2;
  }

/*
   * enforce consistency with polymorphic argument and return types,
   * possibly adjusting return type or declared_arg_types (which will be
   * used as the cast destination by make_fn_arguments)
   * 強制多型引數與返回型別的一致性,可能會調整返回型別或者declared_arg_types
   * (可能會在函式make_fn_arguments中作為轉換目標型別)
   */
  rettype = enforce_generic_type_consistency(actual_arg_types,
                         declared_arg_types,
                         nargs,
                         opform->oprresult,
                         false);

/* perform the necessary typecasting of arguments */
  //引數型別轉換
  make_fn_arguments(pstate, args, actual_arg_types, declared_arg_types);

/* and build the expression node */
  //構建表示式節點
  result = makeNode(OpExpr);
  result->opno = oprid(tup);
  result->opfuncid = opform->oprcode;
  result->opresulttype = rettype;
  result->opretset = get_func_retset(opform->oprcode);
  /* opcollid and inputcollid will be set by parse_collate.c */
  result->args = args;
  result->location = location;

/* if it returns a set, check that's OK */
  if (result->opretset)
  {
    check_srf_call_placement(pstate, last_srf, location);
    /* ... and remember it for error checks at higher levels */
    pstate->p_last_srf = (Node *) result;
  }

ReleaseSysCache(tup);

return (Expr *) result;
}

```

**make_fn_arguments**
給定實際的參數列達式和期望的輸入型別,為表示式樹執行有需要的型別轉換.
```
/*
 * make_fn_arguments()
 *
 * Given the actual argument expressions for a function, and the desired
 * input types for the function, add any necessary typecasting to the
 * expression tree.  Caller should already have verified that casting is
 * allowed.
 * 給定實際的參數列達式和期望的輸入型別,為表示式樹執行有需要的型別轉換.
 * 呼叫者已驗證轉換是可行的.
 *
 * Caution: given argument list is modified in-place.
 * 就地轉換引數型別.
 *
 * As with coerce_type, pstate may be NULL if no special unknown-Param
 * processing is wanted.
 */
void
make_fn_arguments(ParseState *pstate,
          List *fargs,
          Oid *actual_arg_types,
          Oid *declared_arg_types)
{
  ListCell   *current_fargs;
  int     i = 0;

foreach(current_fargs, fargs)
  {
    /* types don't match? then force coercion using a function call... */
    //型別不匹配?
    if (actual_arg_types[i] != declared_arg_types[i])
    {
      //獲取節點
      Node     *node = (Node *) lfirst(current_fargs);

/*
       * If arg is a NamedArgExpr, coerce its input expr instead --- we
       * want the NamedArgExpr to stay at the top level of the list.
       * 如果引數是NamedArgExpr,則強制轉換為輸入表示式
       */
      if (IsA(node, NamedArgExpr))
      {
        NamedArgExpr *na = (NamedArgExpr *) node;

node = coerce_type(pstate,
                   (Node *) na->arg,
                   actual_arg_types[i],
                   declared_arg_types[i], -1,
                   COERCION_IMPLICIT,
                   COERCE_IMPLICIT_CAST,
                   -1);
        na->arg = (Expr *) node;
      }
      else
      {
        //執行轉換
        node = coerce_type(pstate,
                   node,
                   actual_arg_types[i],
                   declared_arg_types[i], -1,
                   COERCION_IMPLICIT,
                   COERCE_IMPLICIT_CAST,
                   -1);
        lfirst(current_fargs) = node;
      }
    }
    i++;
  }
}

```

### 三、跟蹤分析
SQL指令碼
```
testdb=# select * from t_conv where id = '1';
```

跟蹤分析
```
(gdb) b make_op
Breakpoint 2 at 0x619cdb: file parse_oper.c, line 762.
(gdb) c
Continuing.

Breakpoint 2, make_op (pstate=0x22d6e30, opname=0x22d6c48, ltree=0x22d7620, rtree=0x22d7678, last_srf=0x0, location=30)
    at parse_oper.c:762
762   if (rtree == NULL)
(gdb) 
```
呼叫棧資訊
```
(gdb) bt
#0  make_op (pstate=0x22d6e30, opname=0x22d6c48, ltree=0x22d7620, rtree=0x22d7678, last_srf=0x0, location=30)
    at parse_oper.c:859
#1  0x000000000060df9c in transformAExprOp (pstate=0x22d6e30, a=0x22d6ba0) at parse_expr.c:1010
#2  0x000000000060c55b in transformExprRecurse (pstate=0x22d6e30, expr=0x22d6ba0) at parse_expr.c:216
#3  0x000000000060c298 in transformExpr (pstate=0x22d6e30, expr=0x22d6ba0, exprKind=EXPR_KIND_WHERE) at parse_expr.c:155
#4  0x0000000000602164 in transformWhereClause (pstate=0x22d6e30, clause=0x22d6ba0, exprKind=EXPR_KIND_WHERE, 
    constructName=0xb458a7 "WHERE") at parse_clause.c:1691
#5  0x00000000005c7eb5 in transformSelectStmt (pstate=0x22d6e30, stmt=0x22d6c80) at analyze.c:1239
#6  0x00000000005c6392 in transformStmt (pstate=0x22d6e30, parseTree=0x22d6c80) at analyze.c:301
#7  0x00000000005c626d in transformOptionalSelectInto (pstate=0x22d6e30, parseTree=0x22d6c80) at analyze.c:246
#8  0x00000000005c612b in transformTopLevelStmt (pstate=0x22d6e30, parseTree=0x22d6d98) at analyze.c:196
#9  0x00000000005c5f83 in parse_analyze (parseTree=0x22d6d98, sourceText=0x22d5e08 "select * from t_conv where id = '1';", 
    paramTypes=0x0, numParams=0, queryEnv=0x0) at analyze.c:116
#10 0x00000000008e78d9 in pg_analyze_and_rewrite (parsetree=0x22d6d98, 
    query_string=0x22d5e08 "select * from t_conv where id = '1';", paramTypes=0x0, numParams=0, queryEnv=0x0)
    at postgres.c:695
#11 0x00000000008e7f23 in exec_simple_query (query_string=0x22d5e08 "select * from t_conv where id = '1';")
    at postgres.c:1140
#12 0x00000000008ec3a0 in PostgresMain (argc=1, argv=0x22ffdb8, dbname=0x22ffc00 "testdb", username=0x22d2a68 "pg12")
    at postgres.c:4249
#13 0x0000000000843650 in BackendRun (port=0x22f7be0) at postmaster.c:4431
#14 0x0000000000842e2e in BackendStartup (port=0x22f7be0) at postmaster.c:4122
#15 0x000000000083f06a in ServerLoop () at postmaster.c:1704
#16 0x000000000083e920 in PostmasterMain (argc=1, argv=0x22d0a20) at postmaster.c:1377
#17 0x000000000075f834 in main (argc=1, argv=0x22d0a20) at main.c:228
```
輸入引數
運算子為"=";ltree為欄位列,型別為int4(vartype = 23);rtree為常量,型別為unknown(consttype = 705);last_srf為NULL;該運算子在字串中的偏移為30.
```
(gdb) p *pstate
$7 = {parentParseState = 0x0, p_sourcetext = 0x22d5e08 "select * from t_conv where id = '1';", p_rtable = 0x22d72a0, 
  p_joinexprs = 0x0, p_joinlist = 0x22d73a8, p_namespace = 0x22d7328, p_lateral_active = false, p_ctenamespace = 0x0, 
  p_future_ctes = 0x0, p_parent_cte = 0x0, p_target_relation = 0x0, p_target_rangetblentry = 0x0, p_is_insert = false, 
  p_windowdefs = 0x0, p_expr_kind = EXPR_KIND_WHERE, p_next_resno = 2, p_multiassign_exprs = 0x0, p_locking_clause = 0x0, 
  p_locked_from_parent = false, p_resolve_unknowns = true, p_queryEnv = 0x0, p_hasAggs = false, p_hasWindowFuncs = false, 
  p_hasTargetSRFs = false, p_hasSubLinks = false, p_hasModifyingCTE = false, p_last_srf = 0x0, p_pre_columnref_hook = 0x0, 
  p_post_columnref_hook = 0x0, p_paramref_hook = 0x0, p_coerce_param_hook = 0x0, p_ref_hook_state = 0x0}
(gdb) p *opname
$8 = {type = T_List, length = 1, head = 0x22d6c20, tail = 0x22d6c20}
(gdb) p *(Node *)opname->head->data.ptr_value
$9 = {type = T_String}
(gdb) p *(Value *)opname->head->data.ptr_value
$14 = {type = T_String, val = {ival = 12298942, str = 0xbbaabe "="}}
(gdb) p *ltree
$15 = {type = T_Var}
(gdb) p *(Var *)ltree
$16 = {xpr = {type = T_Var}, varno = 1, varattno = 1, vartype = 23, vartypmod = -1, varcollid = 0, varlevelsup = 0, 
  varnoold = 1, varoattno = 1, location = 27}
(gdb) p *(Const *)rtree
$18 = {xpr = {type = T_Const}, consttype = 705, consttypmod = -1, constcollid = 0, constlen = -2, constvalue = 36531016, 
  constisnull = false, constbyval = false, location = 32}  
###
testdb=# select typname from pg_type where oid in (23,705);
 typname 
---------
 int4
 unknown
(2 rows)
###  
```
確定運算子(二元運算子)
```
(gdb) n
769   else if (ltree == NULL)
(gdb) 
779     ltypeId = exprType(ltree);
(gdb) 
780     rtypeId = exprType(rtree);
(gdb) 
781     tup = oper(pstate, opname, ltypeId, rtypeId, false, location);
(gdb) p ltypeId
$19 = 23
(gdb) p rtypeId
$20 = 705
(gdb) 
```
獲取運算子(對應pg_operator)
```
(gdb) n
784   opform = (Form_pg_operator) GETSTRUCT(tup);
(gdb) 
787   if (!RegProcedureIsValid(opform->oprcode))
(gdb) p *opform
$21 = {oid = 96, oprname = {data = "=", '\000' <repeats times="">}, oprnamespace = 11, oprowner = 10, oprkind = 98 'b', 
  oprcanmerge = true, oprcanhash = true, oprleft = 23, oprright = 23, oprresult = 16, oprcom = 96, oprnegate = 518, 
  oprcode = 65, oprrest = 101, oprjoin = 105}
(gdb) 
```
執行型別轉換並構建表示式樹.
實際型別為23&705,運算子宣告操作型別為23&23
```
(gdb) n
798   if (rtree == NULL)
(gdb) 
806   else if (ltree == NULL)
(gdb) 
817     args = list_make2(ltree, rtree);
(gdb) 
818     actual_arg_types[0] = ltypeId;
(gdb) 
819     actual_arg_types[1] = rtypeId;
(gdb) 
820     declared_arg_types[0] = opform->oprleft;
(gdb) 
821     declared_arg_types[1] = opform->oprright;
(gdb) 
822     nargs = 2;
(gdb) p opform->oprleft
$22 = 23
(gdb) p opform->oprright
$23 = 23
(gdb) n
830   rettype = enforce_generic_type_consistency(actual_arg_types,
(gdb) 
```
進入函式make_fn_arguments
```
(gdb) 
837   make_fn_arguments(pstate, args, actual_arg_types, declared_arg_types);
(gdb) n

Breakpoint 1, make_fn_arguments (pstate=0x22d6e30, fargs=0x22d76f8, actual_arg_types=0x7ffda1b2af80, 
    declared_arg_types=0x7ffda1b2af70) at parse_func.c:1835
1835    int     i = 0;
(gdb) 
```
呼叫coerce_type函式執行轉換,unknown -> int4
```
(gdb) n
1837    foreach(current_fargs, fargs)
(gdb) 
1840      if (actual_arg_types[i] != declared_arg_types[i])
(gdb) 
1873      i++;
(gdb) 
1837    foreach(current_fargs, fargs)
(gdb) 
1840      if (actual_arg_types[i] != declared_arg_types[i])
(gdb) p actual_arg_types[i]
$24 = 705
(gdb) p declared_arg_types[i]
$25 = 23
(gdb) n
1842        Node     *node = (Node *) lfirst(current_fargs);
(gdb) n
1848        if (IsA(node, NamedArgExpr))
(gdb) 
1866                     declared_arg_types[i], -1,
(gdb) 
1863          node = coerce_type(pstate,
(gdb) 
1865                     actual_arg_types[i],
(gdb) 
1863          node = coerce_type(pstate,
(gdb) 
1870          lfirst(current_fargs) = node;
(gdb) p *node
$26 = {type = T_Const}
(gdb) p *(Const *)node
$27 = {xpr = {type = T_Const}, consttype = 23, consttypmod = -1, constcollid = 0, constlen = 4, constvalue = 1, 
  constisnull = false, constbyval = true, location = 32}
(gdb) 
```
完成呼叫
```
(gdb) n
1873      i++;
(gdb) 
1837    foreach(current_fargs, fargs)
(gdb) 
1875  }
(gdb) 
make_op (pstate=0x22d6e30, opname=0x22d6c48, ltree=0x22d7620, rtree=0x22d7678, last_srf=0x0, location=30)
    at parse_oper.c:840
840   result = makeNode(OpExpr);
```
回到make_op,構造結果result結構體
```
(gdb) 
841   result->opno = oprid(tup);
(gdb) 
842   result->opfuncid = opform->oprcode;
(gdb) 
843   result->opresulttype = rettype;
(gdb) 
844   result->opretset = get_func_retset(opform->oprcode);
(gdb) 
846   result->args = args;
(gdb) 
847   result->location = location;
(gdb) 
850   if (result->opretset)
(gdb) 
857   ReleaseSysCache(tup);
(gdb) 
859   return (Expr *) result;
(gdb) p *(Expr *) result
$28 = {type = T_OpExpr}
(gdb) p *(OpExpr *) result
$29 = {xpr = {type = T_OpExpr}, opno = 96, opfuncid = 65, opresulttype = 16, opretset = false, opcollid = 0, 
  inputcollid = 0, args = 0x22d76f8, location = 30}
(gdb) 
```
DONE!

**實現函式coerce_type下節再行介紹**

### 四、參考資料
[PostgreSQL Type Conversion](https://www.postgresql.org/docs/11/typeconv-query.html)
[PostgreSQL資料型別轉換規則#1](http://blog.itpub.net/6906/viewspace-2647211/)
[PostgreSQL資料型別轉換規則#2](http://blog.itpub.net/6906/viewspace-2647309/)
[PostgreSQL資料型別轉換規則#3](http://blog.itpub.net/6906/viewspace-2647394/)</repeats>

PostgreSQL 原始碼解讀（202）- 查詢#115(型別轉換)

相關文章