pg数据库double对应oracle,Oracle vs PostgreSQL，研发注意事项（10）- PostgreSQL数据类型转换规则#2...

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)