PostgreSQL的数据类型转换规则有哪些

这篇文章主要介绍“PostgreSQL的数据类型转换规则有哪些”，在日常操作中，相信很多人在PostgreSQL的数据类型转换规则有哪些问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”PostgreSQL的数据类型转换规则有哪些”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

一、函数

函数调用中依赖的指定函数使用以下过程确定.
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=#

到此，关于“PostgreSQL的数据类型转换规则有哪些”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！