openGauss每日一练第12天 | 数据库复合类型的操作

今天学习openGuass的复合类型，有点跟oralce的嵌套表类型相似，不过语法上面相较于oracle,觉得openGauss更顺手些。

一些学习点总结：

• 查询复合类型值：select （表字段).自定义字段 from 表名；
• 自定义枚举类型： as enum 关键字，如表字段引用该类型，插入数据时，必须是该枚举定义的值；
• 枚举类型视图：pg_enum;
• 新增枚举值文中用的语法：
  

ALTER TYPE bugstatus ADD VALUE IF NOT EXISTS 'regress' BEFORE 'closed';
复制

如果确定没有该新增的枚举值也可以：

ALTER TYPE bugstatus ADD VALUE 'XXX';
复制

• 如果类型被引用需要删除时需加上cascade,会把相应引用该类型的表字段也删除掉；

对于枚举类型如何删除其中某个值，目前找不到相关语法；

扩展：https://opengauss.org/zh/docs/2.1.0/docs/Developerguide/CREATE-TYPE.html

有四种形式的CREATE TYPE，分别为：复合类型、基本类型、shell类型和枚举类型，具体说明可查阅官方说明。

语法格式：

CREATE TYPE name AS
    ( [ attribute_name data_type [ COLLATE collation ] [, ... ] ] )

CREATE TYPE name (
    INPUT = input_function,
    OUTPUT = output_function
    [ , RECEIVE = receive_function ]
    [ , SEND = send_function ]
    [ , TYPMOD_IN =
type_modifier_input_function ]
    [ , TYPMOD_OUT =
type_modifier_output_function ]
    [ , ANALYZE = analyze_function ]
    [ , INTERNALLENGTH = { internallength |
VARIABLE } ]
    [ , PASSEDBYVALUE ]
    [ , ALIGNMENT = alignment ]
  [ , STORAGE = storage ]
    [ , LIKE = like_type ]
    [ , CATEGORY = category ]
    [ , PREFERRED = preferred ]
    [ , DEFAULT = default ]
    [ , ELEMENT = element ]
    [ , DELIMITER = delimiter ]
    [ , COLLATABLE = collatable ]
)

CREATE TYPE name

CREATE TYPE name AS ENUM
    ( [ 'label' [, ... ] ] )
复制

课程练习：

1.创建一个复合类型，重命名复合类型，为复合类型增加属性、删除属性

openGauss=# create type t_comp as(a int,b char(30));
CREATE TYPE
openGauss=# alter type t_comp rename to t_comp1;
ALTER TYPE
openGauss=# alter type t_comp1 add attribute c int;
ALTER TYPE
openGauss=# alter type t_comp1 rename attribute a to aa;
ALTER TYPE
openGauss=# \d t_comp1;
  Composite type "public.t_comp1"
 Column |     Type      | Modifiers 
--------+---------------+-----------
 aa     | integer       | 
 b      | character(30) | 
 c      | integer       | 

openGauss=# alter type t_comp1 drop attribute b;
ALTER TYPE
openGauss=# \d t_comp1;
Composite type "public.t_comp1"
 Column |  Type   | Modifiers 
--------+---------+-----------
 aa     | integer | 
 c      | integer | 

openGauss=# 
复制

2.创建一个枚举类型，新增标签值，重命名标签值

openGauss=# create type t_enum as enum('aa','bb','cc');
CREATE TYPE
openGauss=# alter type t_enum add value 'dd';
ALTER TYPE
openGauss=# select * from pg_enum;
 enumtypid | enumsortorder | enumlabel 
-----------+---------------+-----------
     24731 |             1 | aa
     24731 |             2 | bb
     24731 |             3 | cc
     24731 |             4 | dd
(4 rows)

openGauss=# alter type t_enum rename value 'aa' to 'abc';
ALTER TYPE
openGauss=# select * from pg_enum;
 enumtypid | enumsortorder | enumlabel 
-----------+---------------+-----------
     24731 |             2 | bb
     24731 |             3 | cc
     24731 |             4 | dd
     24731 |             1 | abc
(4 rows)
复制

3.使用新创建的类型创建表

openGauss=# create table tt (id int,aa t_comp1,bb t_enum);
CREATE TABLE
openGauss=# insert into tt values(1,(1,1),'abc'),(2,(2,2),'bb');
INSERT 0 2
openGauss=# select * from tt;
 id |  aa   | bb  
----+-------+-----
  1 | (1,1) | abc
  2 | (2,2) | bb
(2 rows)
复制

4.删除类型

openGauss=# drop type t_comp1;
ERROR:  cannot drop type t_comp1 because other objects depend on it
DETAIL:  table tt column aa depends on type t_comp1
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
openGauss=# drop type t_comp1 cascade;
NOTICE:  drop cascades to table tt column aa
DROP TYPE
openGauss=# drop type t_enum cascade;
NOTICE:  drop cascades to table tt column bb
DROP TYPE
openGauss=# \d tt;
      Table "public.tt"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | 

openGauss=# select * from tt;
 id 
----
  1
  2
(2 rows)

openGauss=# drop table tt;
DROP TABLE
复制