openGauss每日一练第12天 | 自定义数据类型

学习目标

学习openGauss定义数据类型

课程学习实操

连接数据库

#第一次进入等待15秒
#数据库启动中...
su - omm
gsql -r

1.创建类型

–创建一种复合类型

CREATE TYPE compfoo AS (f1 int, f2 text);
CREATE TABLE t1_compfoo(a int, b compfoo);
INSERT INTO t1_compfoo values(1,(1,'demo'));
SELECT (b).f1 FROM t1_compfoo;
\d compfoo
omm=# CREATE TYPE compfoo AS (f1 int, f2 text);
CREATE TYPE
omm=# CREATE TABLE t1_compfoo(a int, b compfoo);
CREATE TABLE
omm=# INSERT INTO t1_compfoo values(1,(1,'demo'));
INSERT 0 1
omm=# SELECT (b).f1 FROM t1_compfoo;
 f1 
----
  1
(1 row)

omm=# \d compfoo
Composite type "public.compfoo"
 Column |  Type   | Modifiers 
--------+---------+-----------
 f1     | integer | 
 f2     | text    | 

–创建一个枚举类型

CREATE TYPE bugstatus AS ENUM ('create', 'modify', 'closed');
omm=# CREATE TYPE bugstatus AS ENUM ('create', 'modify', 'closed');
omm=# CREATE TYPE

–查看类型

select * from pg_enum;
omm=# select * from pg_enum;
 enumtypid | enumsortorder | enumlabel 
-----------+---------------+-----------
     16399 |             1 | create
     16399 |             2 | modify
     16399 |             3 | closed
(3 rows)

2.修改类型定义

–重命名数据类型

ALTER TYPE compfoo RENAME TO compfoo1;
omm=# ALTER TYPE compfoo RENAME TO compfoo1;
ALTER TYPE
omm=# 

–增加一个新的属性

ALTER TYPE compfoo1 ADD ATTRIBUTE f3 int;
\d compfoo1
select * from t1_compfoo;
omm=# ALTER TYPE compfoo1 ADD ATTRIBUTE f3 int;
ALTER TYPE

omm=# \d compfoo1
Composite type "public.compfoo1"
 Column |  Type   | Modifiers 
--------+---------+-----------
 f1     | integer | 
 f2     | text    | 
 f3     | integer | 

omm=# select * from t1_compfoo;
 a |     b     
---+-----------
 1 | (1,demo,)
(1 row)

omm=# 

–删除一个属性

ALTER TYPE compfoo1 drop ATTRIBUTE f1;
\d compfoo1
select * from t1_compfoo;
omm=# ALTER TYPE compfoo1 drop ATTRIBUTE f1;
ALTER TYPE
\d compfoo1
omm=# Composite type "public.compfoo1"
 Column |  Type   | Modifiers 
--------+---------+-----------
 f2     | text    | 
 f3     | integer | 

omm=# select * from t1_compfoo;
 a |    b    
---+---------
 1 | (demo,)
(1 row)

omm=# 

–为枚举类型添加一个标签值

ALTER TYPE bugstatus ADD VALUE IF NOT EXISTS 'regress' BEFORE 'closed';
omm=# ALTER TYPE bugstatus ADD VALUE IF NOT EXISTS 'regress' BEFORE 'closed';
ALTER TYPE
omm=# 

–重命名一个标签值

ALTER TYPE bugstatus RENAME VALUE 'create' TO 'new';
select * from pg_enum;
omm=# ALTER TYPE bugstatus RENAME VALUE 'create' TO 'new';
ALTER TYPE
omm=# select * from pg_enum;
 enumtypid | enumsortorder | enumlabel 
-----------+---------------+-----------
     16399 |             2 | modify
     16399 |             3 | closed
     16399 |           2.5 | regress
     16399 |             1 | new
(4 rows)

omm=# 

3.删除类型

DROP TYPE compfoo1;
DROP TYPE compfoo1 cascade;
drop type bugstatus;
omm=# DROP TYPE compfoo1;
ERROR:  cannot drop type compfoo1 because other objects depend on it
DETAIL:  table t1_compfoo column b depends on type compfoo1
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
omm=# DROP TYPE compfoo1 cascade;
NOTICE:  drop cascades to table t1_compfoo column b
DROP TYPE
omm=# drop type bugstatus;
DROP TYPE
omm=# 

课程作业

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

omm=# create type fuhe_type as (f1 int, f2 varchar(30));
CREATE TYPE
omm=# 
omm=# alter type fuhe_type rename to  fuhe_type_new;
ALTER TYPE
omm=# 

omm=# alter type fuhe_type_new add attribute f3 int;
ALTER TYPE

omm=# \d fuhe_type_new
   Composite type "public.fuhe_type_new"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 f1     | integer               | 
 f2     | character varying(30) | 
 f3     | integer    

omm=# alter type fuhe_type_new drop attribute f3;
ALTER TYPE
omm=# \d fuhe_type_new
   Composite type "public.fuhe_type_new"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 f1     | integer               | 
 f2     | character varying(30) | 

omm=# 

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

omm=# create type sex as ENUM('female','male');
CREATE TYPE
omm=# alter type sex add value if not exists 'unknow' before 'male';
ALTER TYPE
omm=# select  * from pg_enum;
 enumtypid | enumsortorder | enumlabel 
-----------+---------------+-----------
     16410 |             1 | female
     16410 |             2 | male
     16410 |           1.5 | unknow
(3 rows)
omm=# alter type sex rename value 'male' to 'nan';
ALTER TYPE
omm=# select  * from pg_enum;
 enumtypid | enumsortorder | enumlabel 
-----------+---------------+-----------
     16410 |             1 | female
     16410 |           1.5 | unknow
     16410 |             2 | nan
(3 rows)

omm=# 

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

omm=# create table test
omm-# (v_sex sex,
omm(#  v_student fuhe_type_new
omm(#  );
CREATE TABLE
omm=# \d test
          Table "public.test"
  Column   |     Type      | Modifiers 
-----------+---------------+-----------
 v_sex     | sex           | 
 v_student | fuhe_type_new | 

omm=# 
omm=#  insert into test values ('female',(1,'sean'));
INSERT 0 1
omm=# select * from test;
 v_sex  | v_student 
--------+-----------
 female | (1,sean)
(1 row)

omm=# 

4.删除类型

omm=#  drop type fuhe_type_new;
ERROR:  cannot drop type fuhe_type_new because other objects depend on it
DETAIL:  table test column v_student depends on type fuhe_type_new
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
omm=# drop table test;
DROP TABLE
omm=#  drop type fuhe_type_new;
DROP TYPE
omm=# drop type sex;
DROP TYPE
omm=# 

学习总结