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

root@modb:~# su - omm
omm@modb:~$ gsql -r
gsql ((openGauss 2.0.0 build 78689da9) compiled at 2021-03-31 21:03:52 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

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 |

omm=# CREATE TYPE bugstatus AS ENUM ('create', 'modify', 'closed');
CREATE TYPE
omm=# select * from pg_enum;
enumtypid | enumsortorder | enumlabel
-----------+---------------+-----------
16399 | 1 | create
16399 | 2 | modify
16399 | 3 | closed
(3 rows)

omm=# ALTER TYPE compfoo RENAME TO compfoo1;
ALTER TYPE
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;
ALTER TYPE
omm=# \d compfoo1
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';
ALTER TYPE
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=# 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 bugstatus;DROP TYPE
omm=#
DROP TYPE
omm=# CREATE TYPE comp_olab AS (com1 int, com2 char(10));
CREATE TYPE
omm=# CREATE TABLE olab_t1(c1 int, c2 comp_olab);
CREATE TABLE
omm=# INSERT INTO olab_t1 values(1,(1,'aaa'));
INSERT 0 1
omm=# SELECT c1,(c2).com1 FROM olab_t1;
omm=# c1 | com1
----+------
1 | 1
(1 row)


omm=# \d comp_olab
Composite type "public.comp_olab"
Column | Type | Modifiers
--------+---------------+-----------
com1 | integer |
com2 | character(10) |

omm=# ALTER TYPE comp_olab RENAME TO comp_olab_2;
ALTER TYPE
omm=# ALTER TYPE comp_olab_2 ADD ATTRIBUTE com3 text;
ALTER TYPE
omm=# \d ALTER TYPE comp_olab_2 ADD ATTRIBUTE com3 text;^C
omm=# \d comp_olab_2
Composite type "public.comp_olab_2"
Column | Type | Modifiers
--------+---------------+-----------
com1 | integer |
com2 | character(10) |
com3 | text |

omm=# insert into olab_t1 values(1,(1,'aaa','bbb'));
INSERT 0 1
omm=# select * from olab_t1;
(2 rows)

omm=# c1 | c2
----+----------------------
1 | (1,"aaa ",)
1 | (1,"aaa ",bbb)

omm=# ALTER TYPE comp_olab_2 drop ATTRIBUTE com1;
ALTER TYPE
omm=# select * from olab_t1;
c1 | c2
----+--------------------
1 | ("aaa ",)
1 | ("aaa ",bbb)
(2 rows)

omm=# CREATE TYPE comp_olab_3 AS ENUM ('test1', 'test2', 'test3');
CREATE TYPE
omm=# ALTER TYPE comp_olab_3 ADD VALUE IF NOT EXISTS 'test4' BEFORE 'test2';
ALTER TYPE
omm=# ALTER TYPE comp_olab_3 RENAME VALUE 'test1' TO 'test5';
ALTER TYPE
omm=# select * from pg_enum;
enumtypid | enumsortorder | enumlabel
-----------+---------------+-----------
16416 | 2 | test2
16416 | 3 | test3
16416 | 1.5 | test4
16416 | 1 | test5
(4 rows)

omm=# DROP TYPE comp_olab_2 cascade;
omm=#
NOTICE: drop cascades to table olab_t1 column c2
DROP TYPE
omm=# drop type comp_olab_3;
DROP TYPE
表仍然存在，但是对应的type列已经删掉
omm=# \d olab_t1;
Table "public.olab_t1"
Column | Type | Modifiers
--------+---------+-----------
c1 | integer |

omm=# drop table olab_t1;
DROP TABLE
omm=#