写点什么

基于 CREATE TYPE 语法自定义新数据类型

  • 2022 年 3 月 11 日
  • 本文字数:2516 字

    阅读完需:约 8 分钟

本文分享自华为云社区《GaussDB(DWS)数据类型之自定义数据类型(复合类型)》,作者: 清道夫。


CREATE TYPE 语法可以在数据库中定义一种新的数据类型。

  • 复合类型 —— 实际上与表相同,但并不会创建一个实际的表。

  • 基本类型 —— 新的基本类型,需要指定对应的外部 input 及 output 函数。

  • shell 类型 —— 占位符

  • 枚举类型 —— 一个非空字符串构成的标签列表

复合类型

语法
CREATE TYPE name AS    ( [ attribute_name data_type [ COLLATE collation ] [, ... ] ] )
复制代码
详解
  1. 首先创建一个新的复合类型包含两个 INT4 数据类型

postgres=# CREATE TYPE point_comlex AS (x INT, y INT);CREATE TYPE
复制代码
  1. 基于创建新的数据类型创建一个表

postgres=# CREATE TABLE position(no INT4, coordinate point_comlex)DISTRIBUTE BY ROUNDROBIN;CREATE TABLE
复制代码
  1. 查询数据

postgres=# SELECT * FROM position; no | coordinate ----+------------  1 | (1,1)(1 row)
-- 注意:SELECT查询语句中不允许对复合类型的某一字段进行查询postgres=# SELECT coordinate.x FROM position;ERROR: missing FROM-clause entry for table "coordinate"LINE 1: SELECT coordinate.x FROM position; ^CONTEXT: referenced column: x

复制代码
  1. 插入数据

-- 整行插入postgres=# INSERT INTO position VALUES(1, (1, 1));INSERT 0 1
-- 只插入某一个字段postgres=# INSERT INTO position(coordinate.x) VALUES(2);INSERT 0 1postgres=# SELECT * FROM position; no | coordinate ----+------------ 1 | (1,1) | (2,)(2 rows)

复制代码
  1. 更新数据

-- 按列更新postgres=# UPDATE position SET coordinate=(10,20) WHERE no=1;UPDATE 1postgres=# SELECT * FROM position; no | coordinate ----+------------    | (2,)  1 | (10,20)(2 rows)
-- 单字段更新postgres=# UPDATE position SET coordinate.y=2 WHERE no is null;UPDATE 1postgres=# SELECT * FROM position; no | coordinate ----+------------ 1 | (10,20) | (2,2)(2 rows)
postgres=# UPDATE position SET position.coordinate.y=3 WHERE no is null;UPDATE 1postgres=# SELECT * FROM position; no | coordinate ----+------------ 1 | (10,20) | (2,3)(2 rows)

复制代码
彩蛋

到这基本就结束了,细心的小伙伴可能会问,如果表名、列名、字段名相同是否会有歧义呢,数据库又是如何处理歧义的?

-- 新建一个复合类型postgres=# CREATE TYPE newtype AS(no INT, info text);CREATE TYPE-- 创建一个表,表名、列名均与复合类型中的字段名相同postgres=# CREATE TABLE info(no INT, info newtype)DISTRIBUTE BY ROUNDROBIN;CREATE TABLE-- 插入一条数据postgres=# INSERT INTO info VALUES(1, (1, 'MIKE'));INSERT 0 1-- 查询postgres=# SELECT * FROM info; no |   info   ----+----------  1 | (1,MIKE)(1 row)

复制代码

此时表名、列名与复合类型中的字段名均相同,那 info.info 既可以是表名.列名又可以使列名.字段名,实际上是什么呢?

postgres=# UPDATE info SET info.info='JACK' WHERE no=1;NOTICE:  update field 'info' of column 'info', though it's ambiguous.UPDATE 1postgres=# SELECT * FROM info; no |   info   ----+----------  1 | (1,JACK)(1 row)

复制代码

通过执行的提示信息我们可以看出,数据库发现了歧义。而且最终更新的是列中的字段。从这可以看出,对于有歧义的更新,数据库的处理是有优先级的定义的,此处是列名.字段名 > 表名.列名

此处还有一个疑问,那如果 schema 的名字也相同,数据库如何处理呢?

-- 创建SCHEMApostgres=# CREATE SCHEMA info;CREATE SCHEMA-- 设置为当前SCHEMApostgres=# SET CURRENT_SCHEMA=info;SET-- 创建复合类型postgres=# CREATE TYPE newtype AS(no INT, info text);CREATE TYPE-- 创建与复合类型字段名相同的表名postgres=# CREATE TABLE info(no INT, info newtype)DISTRIBUTE BY ROUNDROBIN;CREATE TABLE-- 插入数据postgres=# INSERT INTO info VALUES(1, (1, 'MIKE'));INSERT 0 1-- 歧义场景更新,优先更新列中的字段postgres=# UPDATE info SET info.info='JACK' WHERE no=1;NOTICE:  update field 'info' of column 'info', though it's ambiguous.UPDATE 1postgres=# SELECT * FROM info; no |   info   ----+----------  1 | (1,JACK)(1 row)
-- info.info.info代表的是表名.列名.字段名postgres=# UPDATE info SET info.info.info='TOM' WHERE no=1;UPDATE 1postgres=# SELECT * FROM info; no | info ----+--------- 1 | (1,TOM)(1 row)
-- info.info.no代表的也是表名.列名.字段名,此处有歧义但好像没提示,为什么呢?接着往下看postgres=# UPDATE info SET info.info.no=2 WHERE no=1;UPDATE 1postgres=# SELECT * FROM info; no | info ----+--------- 1 | (2,TOM)(1 row)-- info.info.info.info更新报错,也就是说SET后不能使用schema名称,也就解释了上面的语句没有歧义提示postgres=# UPDATE info SET info.info.info.info='JACK' WHERE no=1;ERROR: cannot assign to field "info" of column "info" because its type text is not a composite typeLINE 1: UPDATE info SET info.info.info.info='JACK' WHERE no=1; ^CONTEXT: referenced column: info

复制代码

可以看出 UDPATE 的 SET 中不能出现 schema,否则会报错

-- 创建一个新的表postgres=# CREATE TABLE test(a INT)DISTRIBUTE BY ROUNDROBIN;CREATE TABLE-- 更新时指定schemapostgres=# UPDATE test SET info.test.a=1;ERROR:  column "info.test" of relation "test" does not existLINE 1: UPDATE test SET info.test.a=1;                        ^
复制代码

从上述报错提示可以看出,数据库将 SET 之后的 info.test 认为是 test 表的一列。而表定义中没有,因此必然报错。


想了解 GuassDB(DWS)更多信息,欢迎微信搜索“GaussDB DWS”关注微信公众号,和您分享最新最全的 PB 级数仓黑科技,后台还可获取众多学习资料哦~


点击关注,第一时间了解华为云新鲜技术~​


发布于: 刚刚阅读数: 2
用户头像

提供全面深入的云计算技术干货 2020.07.14 加入

华为云开发者社区,提供全面深入的云计算前景分析、丰富的技术干货、程序样例,分享华为云前沿资讯动态,方便开发者快速成长与发展,欢迎提问、互动,多方位了解云计算! 传送门:https://bbs.huaweicloud.com/

评论

发布
暂无评论
基于CREATE TYPE语法自定义新数据类型_数据库_华为云开发者社区_InfoQ写作平台