PostgreSQL应用手册 第六章

 

第六章

● 类型转换

● 内容

● 概述

● 操作符

● 函数

● 查询目标

● UNION 查询


SQL 查询可能（有意无意地）要求在同一表达式里混合不同的数据类型.Postgres 在计算混合类型表达式方面有许多扩展性很强的功能。

在大多数情况下，用户不需要明白类型转换机制的细节．但是，由Postgres 所进行的隐含的类型转换会对查询的结果产生明显的影响，这些影响又可以由用户或程序员通过显式的类型转换进行剪裁利用。

本章介绍Postgres 类型转换的传统和机制。关于特定的类型和函数及操作符的进一步信息，请参考用户手册和程序员手册有关章节。

在程序员手册里有关于隐含类型转换的具体算法的更详细的介绍。


▲ 概述

SQL 是强类型语言。也就是说，每一数据都与一个决定其特性和用法的数据类型相联。Postgres有一个可扩展的数据类型系统，该系统比其他RDBMS 对应系统的实现更具通用性和灵活性。因而，Postgres大多数类型转换的特性应该是由通用规则来管理的，而不是由专门搜索方法来分析混合类型表达式的含义，即便是用户定义的类型也应该如此。

Postgres 扫描/分析器只将语法元素分解成五个基本种类：整数（integers），浮点数（floats），字符串（strings），名字（names）和关键字（keywords）。大多数扩展的类型首先表征为字符串（strings）。SQL 语言的定义允许将类型名声明为字符串，这个机制被Postgres 用于令分析器沿着正确的方向运行。例如，下面查询 :




有两个字符串（strings）类型text 和point。如果类型没有声明，（该类型）先被初始化成一个拥有存储空间的unknown（未知）类型，该类型将在后面描述的晚期阶段分析。
在Postgres 分析器里，有四种基本的SQL 元素需要独立的类型转换规则：


▲ 操作符

Postgres 允许表达式里使用双目操作符（两个参数），也允许使用左目或右目操作符（单目操作符，一个参数）。



▲ 函数调用

多数Postgres 类型系统是建筑在一套丰富的函数上的。任何查询中调用的函数的参数，不管是一个还是多个，必须符合该函数在系统表里的定义．


▲ 查询目标

SQL INSERT 语句将查询结果插入表中．查询表达式的类型必须和insert语句的目标列一致或者是（可能需要）转换成一致的．


▲ 联合(UNION)查询

因为 UNION SELECT 语句的所有查询结果必须在一列里显示出来，所以每个 SELECT子句中的元素类型必须相互匹配并转换成一套统一类型。
许多通用的类型转换规则使用建立在Postgres 函数和操作符系统表基础上的简单转换来实现．在转换规则中包括了一些启发方法，以便更好的支持SQL92 标准中一些传统的内部类型，像smallint，integer，和float。
Postgres 分析器的习惯是：所有类型转换函数接受一个类型是源类型的参数，该参数与目标类型同名．任何符合这一标准的函数都被认为是一个有效的转换函数，因而可以被分析器当作转换函数使用．这个简单的假设令分析器在不需要写硬代码的基础上就可以充分利用类型转换的能力，也让用户定义的类型可以透明地使用同一特性。


分析器中还有一个搜索器用于更好地猜测SQL 标准类型的确切特性。分析器里有五种定义的类型：布尔（boolean），字符串（string），数字（numeric），几何（geometric）和用户定义（user-defined）。除用户定义类型外，每种类型都有一种“优选类型”用于解决类型定义不明确的问题．每一个"用户定义"的类型就是其自身的“优选类型”，所以那些含混不清的表达式（在分析结果中有多种可能的表达式）如果只有一个用户定义类型则可以正确分析，而那些有多个用户定义类型的表达式会仍然是含糊的，因而会抛出分析错。
含糊的表达式如果其多种候选可能性都是同一种数据类型，则有可能被正确分析，而如果其候选可能性是几种数据类型，则分析很可能会报错，并要求用户将表达式明确化。



▲ 要决

所有类型转换规则都是建立在下面几个基本原则上的：
隐含转换决不能有奇怪的或不可预见的输出。
用户定义类型，因为分析器对其没有预先的认识，在类型级别中应该级别较“高”。在混合类型的表达式里，内部类型总是应该转换成用户定义类型。（当然只是在必须转换的时候）。

用户定义类型是不相关的。当前，Postgres 除了用于内部数据类型的硬代码搜索器和以系统表中现有函数为基础的隐含类型关系外，没有任何可用于处理类型间关系的信息。

如果一个查询不需要隐含的类型转换，分析器或执行器不应该进行更多的额外操作。这就是说，任何一个类型匹配，格式清晰的查询不应该在分析器里耗费更多的时间，也不应该做任何不必要的类型转换。

另外，如果一个查询通常使用某个函数进行隐含类型转换，而用户显式定义了一个有正确参数的函数，解释器应该使用新函数取代原先旧函数的隐含操作。



▲ 操作符

转换过程

操作符计算

1. 在 pg_operator 系统表里找出准确的匹配。

a. 如果一个双目操作符的一个参数是unknown．则假设其与另一个参数类型相同。

b. 掉转参数，寻找一个做为反向操作符匹配时指向自身操作符。如果找到，则掉转分析树上的参数并使用该操作符。


2. 寻找最优匹配

a. 生成一个同名的操作符的列表。

b. 如果表中只有一个操作符：如果输入类型可以匹配或转换，则使用该操作符，否则输出一个错误。

c. 挑出具有最多显式的类型匹配的操作符．挑出所有没有显式的类型匹配的操作符并进行下一步．如果结果只有一个候选操作符，而且类型匹配或可转换．则使用之。

d. 如果输入参数是“unknown”， 将输入按布尔（boolean），数字（numeric），字符串（string），几何（geometric）或用户定义（user-defined）分类。如果分类结果有多种类型，或超过一种用户定义类型，则产生一个错误，因为在没有更多线索的条件下不能导出正确的选择。如果分类结果只有一类，则将先前“unknown”的类型转为“优选类型”。

e. 挑出类型匹配最准确的和从前一步中挑出的匹配各类的“优选类型”的操作符。如果还有超过一个的候选操作符或是没有候选操作符，则产生一个错误。


例子

指数操作符
在分类里只有一个指数操作符，它以float8 作为参数。扫描器给下面查询表达式的两个参数赋予int4 的初始类型：





分析器对两个参数都做类型转换，查询等效于：





※ 注意：最后的形式最高效，因为不用调用函数做隐含类型转换。这对小查询没有什么影响，但可能对那些操作大表的查询的性能产生较大影响。


字符串连接一种类字符串的语法既可以用于字符串也可以用于复杂的扩展类型。包含不明类型的字串使用可能的候选操作符匹配。
有一个未声明的参数：





本例中分析器寻找一个两个参数都是text 的操作符。因为有一个这样的操作符，它认为另一个参数的类型是text。
联接未声明类型：





本例中对类型的初始值没有任何暗示，因为查询中没有声明任何类型。因此，分析器查找所有参数都是字符串类的候选操作符。并且它选择“优选类型”，text，作为本查询字符串类的类型。
注意: 如果用户定义了一个新的数据类型，并且定义了用于该类型的操作符“||”，那么本查询将不会象上面写的那样成功完成。这时分析器会因为现在有两类候选操作符而无法决定使用哪个。
阶乘
本例演示了一个有趣的结果。一般来说，阶乘只用于整数，Postgres 操作符类别中用于阶乘的只有一个，其以整数为操作数。如果输入一个非整数数字参数，Postgres 将试图把该参数转换成整数进行阶乘运算。





※ 注意: 这样做当然会导致一个数学上有疑问的结果，因为非整数的阶乘原则没有定义。但是，数据库的角色不是数学教学，而是数据操作．如果用户一定要进行浮点数的阶乘，Postgres 将尽可能服从。


▲ 函数

函数计算

1. 在 pg_proc 系统表里查找准确的匹配。

2. 查找最优的匹配

a. 生成一个具有相同数量参数的同名函数的列表。

b. 如果表中只有一个函数，而且该函数的输入类型可以匹配或转换则使用之，如果不能，生成一个错误。
c. 找出所有具有最多显式参数类型匹配的函数。如果没有显式参数匹配的函数则进行下一步。如果只有一个候选函数，而且其参数类型可以匹配或转换，使用之。

d. 如果任何输入参数类型是“未知”(“unknown”)，将输入的参数分类成布尔（ boolean），数字（numeric），字符串（string），几何（geometric）和用户定义（user-defined）类型．如果分类后有多于一类，或多于一种用户定义类型则生成一个错误，因为在没有更多线索的情况下不能导出正确的选择。如果分类结果只有一类，则将原先的“未知”（“unknown”）类型转换成该类的“优选类型”（“preferred type”）。

e. 挑出类型匹配最准确的函数以及在上一步里每一类中匹配该类的"优选类型"（"preferred type"）的函数．如果这时仍然有多个函数可选，或一个都没有，则生成一个错误。


例子

阶乘函数
在 pg_proc 系统表里只定义了一个阶乘函数．所以下面的查询自动将int2 参数转换成int4：





实际上它被分析器转换成：





子字串函数
在pg_proc 里定义了两个 substr 函数．但是，其中只有一个使用两个参数，参数类型分别是text 和int4。
如果其中一个字符串常量的类型不明确，其类型直接与唯一的候选函数匹配：





如果该字符串定义为类型varchar，就像大多数从表中取来的数据一样，分析器将试着将其转换成text：





被分析器转换后变成：





※ 注意：分析器中有一些搜索器用于优化char，varchar，和text 类型之间的关系。这时，substr 将直接用varchar 字符串调用，而不是在其中插入一个显式的类型转换调用。
而且，如果以int4 为参数调用函数，分析器将试图将其转换成text：





▲ 查询目标

目标计算

查找与目标准确的匹配。
如果必要的话，试着将表达式直接转换成目标类型。
如果目标是定长类型（如 char 或定义了长度的 varchar）则试着找出一个有两个参数的同名定长函数，该函数第一个参数类型为名称，第二个参数类型是整形长度。

例子

varchar 存储

对一个目标列定义为 varchar(4) 的查询，下面查询确保目标的正确定长：





▲ UNION （联合）查询

UNION 元素有些特别，因为它必须匹配一些也许不太类似的类型以生成一个唯一的结果集。
UNION 计算

检测标明的类型，获得所有结果．
使用UNION语句转换所有结果，使之与第一个 SELECT 语句或目标列匹配．

例子

待定类型





转换的 UNION

union 的类型将被强制与union的第一个/顶端的语句的类型相同。





一个可选的分析器策略是从一组数据中选择"最好"的类型，但这却难以在分析器优良的递归技术中实现．不过，"最好"的类型在使用 into 向表中插入数据时使用：






请继续看第七章..........

出处：南方Linux