PostgreSQL 10.1 手册_部分 II. SQL 语言_第 8 章 数据类型_8.16. 复合类型

本文涉及的产品
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云原生数据库 PolarDB PostgreSQL 版,企业版 4核16GB
推荐场景:
HTAP混合负载
云原生数据库 PolarDB MySQL 版,通用型 2核4GB 50GB
简介: 8.16. 复合类型 8.16.1. 复合类型的声明 8.16.2. 构造组合值 8.16.3. 访问复合类型 8.16.4. 修改复合类型 8.16.5. 在查询中使用复合类型 8.16.6. 复合类型输入和输出语法 一个复合类型表示一行或一个记录的结构,它本质上就是一个域名和它们数据类型的列表。

8.16. 复合类型

一个复合类型表示一行或一个记录的结构,它本质上就是一个域名和它们数据类型的列表。PostgreSQL允许把复合类型用在很多能用简单类型的地方。例如,一个表的一列可以被声明为一种复合类型。

8.16.1. 复合类型的声明

这里有两个定义复合类型的简单例子:

CREATE TYPE complex AS (
    r       double precision,
    i       double precision
);

CREATE TYPE inventory_item AS (
    name            text,
    supplier_id     integer,
    price           numeric
);

该语法堪比CREATE TABLE,不过只能指定域名和类型,当前不能包括约束(例如NOT NULL)。注意AS关键词是必不可少的,如果没有它,系统将认为用户想要的是一种不同类型的CREATE TYPE命令,并且你将得到奇怪的语法错误。

定义了类型之后,我们可以用它们来创建表:

CREATE TABLE on_hand (
    item      inventory_item,
    count     integer
);

INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);

or functions:

CREATE FUNCTION price_extension(inventory_item, integer) RETURNS numeric
AS 'SELECT $1.price * $2' LANGUAGE SQL;

SELECT price_extension(item, 10) FROM on_hand;

只要你创建了一个表,也会自动创建一个复合类型来表示表的行类型,它具有和表一样的名称。例如,如果我们说:

CREATE TABLE inventory_item (
    name            text,
    supplier_id     integer REFERENCES suppliers,
    price           numeric CHECK (price > 0)
);

那么和上面所示相同的inventory_item复合类型将成为一种副产品,并且可以按上面所说的进行使用。不过要注意当前实现的一个重要限制:因为没有约束与一个复合类型相关,显示在表定义中的约束不会应用于表外复合类型的值(一种部分的变通方案是用域类型作为复合类型的成员)。

8.16.2. 构造组合值

要把一个组合值写作一个文字常量,将该域值封闭在圆括号中并且用逗号分隔它们。你可以在任何域值周围放上双引号,并且如果该域值包含逗号或圆括号则必须这样做(更多细节见下文)。这样,一个组合常量的一般格式是下面这样的:

'( val1 , val2 , ... )'

一个例子是:

'("fuzzy dice",42,1.99)'

这将是上文定义的inventory_item类型的一个合法值。要让一个域为 NULL,在列表中它的位置上根本不写字符。例如,这个常量指定其第三个域为 NULL:

'("fuzzy dice",42,)'

如果你写一个空字符串而不是 NULL,写上两个引号:

'("",42,)'

这里第一个域是一个非 NULL 空字符串,第三个是 NULL。

(这些常量实际上只是第 4.1.2.7 节中讨论的一般类型常量的特殊类型。 该常量最初被当做一个字符串并且被传递给复合类型输入转换例程。 可能需要一次显式类型声明来说明将该常量转换为哪个类型)。

ROW表达式也能被用来构建组合值。在大部分情况下,比起使用字符串语法, 这相当简单易用,因为你不必担心多层引用。我们已经在上文用过这种方法:

ROW('fuzzy dice', 42, 1.99)
ROW('', 42, NULL)

只要在表达式中有多于一个域,ROW 关键词实际上就是可选的,因此这些可以简化成:

('fuzzy dice', 42, 1.99)
('', 42, NULL)

第 4.2.13 节中更加详细地讨论了ROW表达式语法。

8.16.3. 访问复合类型

要访问一个组合列的一个域,可以写成一个点和域的名称,更像从一个表名中选择一个域。事实上,它太像从一个表名中选择,这样我们不得不使用圆括号来避免让解析器混淆。例如,你可能尝试从例子表on_hand中选取一些子域:

SELECT item.name FROM on_hand WHERE item.price > 9.99;

这不会有用,因为名称item会被当成是一个表名,而不是on_hand的一个列名。你必须写成这样:

SELECT (item).name FROM on_hand WHERE (item).price > 9.99;

或者你还需要使用表名(例如在一个多表查询中),像这样:

SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99;

现在加上括号的对象就被正确地解释为对item列的引用,然后可以从中选出子域。

只要你从一个组合值中选择一个域,相似的语法问题就适用。例如,要从一个返回组合值的函数的结果中选取一个域,你需要这样写:

SELECT (my_func(...)).field FROM ...

如果没有额外的圆括号,这将生成一个语法错误。

特殊字段名称 * 意味着 所有字段,更多解释请见 第 8.16.5 节

8.16.4. 修改复合类型

这里有一些插入和更新组合列的正确语法的例子。首先,插入或者更新一整个列:

INSERT INTO mytab (complex_col) VALUES((1.1,2.2));

UPDATE mytab SET complex_col = ROW(1.1,2.2) WHERE ...;

第一个例子忽略ROW,第二个例子使用它,我们可以用两者之一完成。

我们能够更新一个组合列的单个子域:

UPDATE mytab SET complex_col.r = (complex_col).r + 1 WHERE ...;

注意这里我们不需要(事实上也不能)把圆括号放在正好出现在SET之后的列名周围,但是当在等号右边的表达式中引用同一列时确实需要圆括号。

并且我们也可以指定子域作为INSERT的目标:

INSERT INTO mytab (complex_col.r, complex_col.i) VALUES(1.1, 2.2);

如果我们没有为该列的所有子域提供值,剩下的子域将用空值填充。

8.16.5. 在查询中使用复合类型

在查询中的复合类型有各种特殊语法规则和行为。这些规则提供有用的短写, 但是如果不知道背后的逻辑,可能会混淆。

PostgreSQL中,在查询中引用表名(或别名) 是对该表的当前行的复合类型的有效引用。例如,如果我们有下面这样一个表 inventory_item

SELECT c FROM inventory_item c;

这个查询产生一个复合类型列,所以我们得到的输出像:

           c
------------------------
 ("fuzzy dice",42,1.99)
(1 row)

不过请注意,简单名称首先匹配列名然后再匹配表名, 所以这个示例能有效是因为查询表中没有列的名字为c

普通限定列名称语法table_name.column_name 可以理解为对表的当前行的组合值应用域选择。 (出于效率原因,实际上不是以这种方式实现的。)

当我们写

SELECT c.* FROM inventory_item c;

然后,根据SQL标准,我们应该得到扩展为独立列的表的内容:

    name    | supplier_id | price
------------+-------------+-------
 fuzzy dice |          42 |  1.99
(1 row)

就像查询是

SELECT c.name, c.supplier_id, c.price FROM inventory_item c;

PostgreSQL将为任意组合值表达式应用这种扩展行为, 尽管就像上面所示, .*被应用到不仅仅是一个简单的表名时,需要给值加括号。 例如,如果myfunc()是一个返回复合类型的函数, 有abc列, 那么这两个查询有相同的结果:

SELECT (myfunc(x)).* FROM some_table;
SELECT (myfunc(x)).a, (myfunc(x)).b, (myfunc(x)).c FROM some_table;

提示

PostgreSQL通过实际转换第一种形式到第二种形式处理列扩展。 所以,在这个示例中,两种语法都是每行调用三次myfunc()。 如果你希望避免这种昂贵的函数,可以使用这样的查询:

SELECT (m).* FROM (SELECT myfunc(x) AS m FROM some_table OFFSET 0) ss;

OFFSET 0子句使优化器避免膨胀子查询多次调用myfunc()

composite_value.*语法出现在 SELECT输出列表、 INSERT/UPDATE/DELETE中的 RETURNING列表、 VALUES子句或 行构造 的顶层时,它会导致这种形式的列扩展。 在所有其他内容(包括嵌套在其中一种构造之中时), 给组合值附加.*不会改变值,因为它意味着所有列, 因此会再次产生相同的组合值。例如,如果somefunc() 接受组合值参数,那么这些查询是相同的:

SELECT somefunc(c.*) FROM inventory_item c;
SELECT somefunc(c) FROM inventory_item c;

在两种情况下,将inventory_item的当前行作为一个组合值参数传递给函数。 尽管.*在这种情况下什么也不做,但是使用它是好的风格, 因为它使组合值变得清晰。特别是,分析器将会认为c.*中的c 引用一个表名或别名,而不是列名,所以这样就不会有混淆;而没有.*, 就不清楚c意味着一个表名或是一个列名,并且实际上, 如果有个列名为c,将会更加倾向于列名的解释。

另一个示例展示了所有这些查询都是相同的事情:

SELECT * FROM inventory_item c ORDER BY c;
SELECT * FROM inventory_item c ORDER BY c.*;
SELECT * FROM inventory_item c ORDER BY ROW(c.*);

所有这些ORDER BY子句都指定行的组合值,导致根据 第 9.23.6 节中描述的规则进行行排序。 不过,如果inventory_item包含一个名为c的列, 那么第一种形式将与其他两种不同,因为它意味着仅根据该列进行排序。 像前面那样给出列名,下面这些查询与上面那些相等:

SELECT * FROM inventory_item c ORDER BY ROW(c.name, c.supplier_id, c.price);
SELECT * FROM inventory_item c ORDER BY (c.name, c.supplier_id, c.price);

(后面的查询省略关键字ROW使用了行构造器。)

与组合值相关的另一个特殊语法行为是我们可以使用函数表示法 提取一个组合值的字段。简单解释就是符号field(table) 和table.field可以互换。 例如,这些查询是相等的:

SELECT c.name FROM inventory_item c WHERE c.price > 1000;
SELECT name(c) FROM inventory_item c WHERE price(c) > 1000;

另外,如果我们有一个接受复合类型参数的函数,我们可以用任意表示方式调用它。 这些查询是相等的:

SELECT somefunc(c) FROM inventory_item c;
SELECT somefunc(c.*) FROM inventory_item c;
SELECT c.somefunc FROM inventory_item c;

函数表示法和字段表示法之间的相等性使在复合类型上使用函数来实现经过计算的字段成为可能。 使用上面最后一个查询的应用不需要直接知道somefunc 不是该表的一个实际列。

提示

因为该行为,给一个接受复合类型参数的函数提供与该复合类型的任意字段相同的名称是不明智的。 如果有歧义,将首选解释为字段名,所以不能没有技巧的调用这种函数。 强制函数解释的一种方法是对函数名称进行模式限定,也就是, 写schema.func(compositevalue)

8.16.6. 复合类型输入和输出语法

一个组合值的外部文本表达由根据域类型的 I/O 转换规则解释的项,外加指示组合结构的装饰组成。装饰由整个值周围的圆括号(()),外加相邻项之间的逗号(,)组成。圆括号之外的空格会被忽略,但是在圆括号之内空格会被当成域值的一部分,并且根据域数据类型的输入转换规则可能有意义,也可能没有意义。例如,在

'(  42)'

中,如果域类型是整数则空格会被忽略,而如果是文本则空格不会被忽略。

如前所示,在写一个组合值时,你可以在任意域值周围写上双引号。如果不这样做会让域值迷惑组合值解析器,你就必须这么做。特别地,包含圆括号、逗号、双引号或反斜线的域必须用双引号引用。要把一个双引号或者反斜线放在一个被引用的组合域值中,需要在它前面放上一个反斜线(还有,一个双引号引用的域值中的一对双引号被认为是表示一个双引号字符,这和 SQL 字符串中单引号的规则类似)。另一种办法是,你可以避免引用以及使用反斜线转义来保护所有可能被当作组合语法的数据字符。

一个全空的域值(在逗号或圆括号之间完全没有字符)表示一个 NULL。要写一个空字符串值而不是 NULL,可以写成""

如果域值是空串或者包含圆括号、逗号、双引号、反斜线或空格,组合输出例程将在域值周围放上双引号(对空格这样处理并不是不可缺少的,但是可以提高可读性)。嵌入在域值中的双引号及反斜线将被双写。

注意

记住你在一个 SQL 命令中写的东西将首先被解释为一个字符串,然后才会被解释为一个组合。这就让你所需要的反斜线数量翻倍(假定使用了转义字符串语法)。例如,要在组合值中插入一个含有一个双引号和一个反斜线的text域,你需要写成:

INSERT ... VALUES (E'("\\"\\\\")');

字符串处理器会移除一层反斜线,这样在组合值解析器那里看到的就会是("\"\\")。接着,字符串被交给text数据类型的输入例程并且变成"\(如果我们使用的数据类型的输入例程也会特别处理反斜线,例如bytea,在命令中我们可能需要八个反斜线用来在组合域中存储一个反斜线)。美元引用(见第 4.1.2.4 节)可以被用来避免双写反斜线。

提示

当在 SQL 命令中书写组合值时,ROW构造器语法通常比组合文字语法更容易使用。在ROW中,单个域值可以按照平时不是组合值成员的写法来写。

本文转自PostgreSQL中文社区,原文链接:8.16. 复合类型

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
1月前
|
SQL DataWorks NoSQL
DataWorks产品使用合集之如何将SQL Server中的数据转存到MongoDB
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
253 1
|
2天前
|
SQL DataWorks 关系型数据库
DataWorks产品使用合集之数据集成时源头提供数据库自定义函数调用返回数据,数据源端是否可以写自定义SQL实现
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
|
1天前
|
SQL 分布式计算 DataWorks
MaxCompute操作报错合集之使用sql查询一个表的分区数据时遇到报错,该如何解决
MaxCompute是阿里云提供的大规模离线数据处理服务,用于大数据分析、挖掘和报表生成等场景。在使用MaxCompute进行数据处理时,可能会遇到各种操作报错。以下是一些常见的MaxCompute操作报错及其可能的原因与解决措施的合集。
|
1天前
|
SQL Oracle 关系型数据库
关系型数据库Oracle备份类型
【7月更文挑战第18天】
12 2
|
8天前
|
SQL 存储 关系型数据库
SQL分类与数据类型
【7月更文挑战第12天】Mysql SQL语句分类与数据类型 介绍
|
11天前
|
SQL 数据库
【SQL】已解决:SQL分组去重并合并相同数据
【SQL】已解决:SQL分组去重并合并相同数据
22 1
|
16天前
|
SQL 自然语言处理 关系型数据库
PolarDB自然语言到SQL语言转义
PolarDB自然语言到SQL语言转义
|
18天前
|
存储 关系型数据库 MySQL
|
1天前
|
SQL 存储 Oracle
TDengine 3.3.2.0 发布:新增 UDT 及 Oracle、SQL Server 数据接入
**TDengine 3.3.2.0 发布摘要** - 开源与企业版均强化性能,提升WebSocket、stmt模式写入与查询效率,解决死锁,增强列显示。 - taos-explorer支持geometry和varbinary类型。 - 企业版引入UDT,允许自定义数据转换。 - 新增Oracle和SQL Server数据接入。 - 数据同步优化,支持压缩,提升元数据同步速度,错误信息细化,支持表名修改。 - 扩展跨平台支持,包括麒麟、Euler、Anolis OS等。
9 0
|
26天前
|
SQL druid Java
传统后端SQL数据层替代解决方案: 内置数据源+JdbcTemplate+H2数据库 详解
传统后端SQL数据层替代解决方案: 内置数据源+JdbcTemplate+H2数据库 详解
19 1