修改表名
在MogDB2.0.1/OpenGauss2.0.1/Postgres13.4数据库中可以通过alter table 命令来修改表名：
alter table 表名 rename to 新表名；
下面我们来测试一下，修改表名后，与原表相关联的一些数据库对象状态会有什么变化？
首先，创建测试表及相关对象（比如：索引，序列，视图，函数）。
MogDB/OpenGauss/PostgreSQL数据库，分别进行如下操作：
create table test (id1 serial PRIMARY KEY,id2 int,name varchar(10));
CREATE SEQUENCE s_test_1 OWNED BY test.id2;
create index idx_test_name on test(name);
create view v_test as select * from test;
CREATE OR REPLACE FUNCTION func_test1() RETURNS setof bigint AS $$
BEGIN
return query select count(*) from test;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION func_test2() RETURNS setof bigint AS $$
BEGIN
return query select count(*) from public.test;
END;
$$ LANGUAGE plpgsql;
insert into test(id2,name) values(nextval('s_test_1'),'test1');
MogDB数据库修改表名操作：
#修改表名前
enmo=# \d+ *test*
Index "public.idx_test_name"
Column | Type | Definition | Storage
--------+-----------------------+------------+----------
name | character varying(10) | name | extended
btree, for table "public.test"
Sequence "public.s_test_1"
Column | Type | Value | Storage
---------------+---------+---------------------+---------
sequence_name | name | s_test_1 | plain
last_value | bigint | 1 | plain
start_value | bigint | 1 | plain
increment_by | bigint | 1 | plain
max_value | bigint | 9223372036854775807 | plain
min_value | bigint | 1 | plain
cache_value | bigint | 1 | plain
log_cnt | bigint | 32 | plain
is_cycled | boolean | f | plain
is_called | boolean | t | plain
uuid | bigint | 0 | plain
Owned by: public.test.id2
Table "public.test"
Column | Type | Modifiers | Storage | Stats target | Description
--------+-----------------------+----------------------------------------------------+----------+--------------+-------------
id1 | integer | not null default nextval('test_id1_seq'::regclass) | plain | |
id2 | integer | | plain | |
name | character varying(10) | | extended | |
Indexes:
"test_pkey" PRIMARY KEY, btree (id1) TABLESPACE pg_default
"idx_test_name" btree (name) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no
Sequence "public.test_id1_seq"
Column | Type | Value | Storage
---------------+---------+---------------------+---------
sequence_name | name | test_id1_seq | plain
last_value | bigint | 1 | plain
start_value | bigint | 1 | plain
increment_by | bigint | 1 | plain
max_value | bigint | 9223372036854775807 | plain
min_value | bigint | 1 | plain
cache_value | bigint | 1 | plain
log_cnt | bigint | 32 | plain
is_cycled | boolean | f | plain
is_called | boolean | t | plain
uuid | bigint | 0 | plain
Owned by: public.test.id1
Index "public.test_pkey"
Column | Type | Definition | Storage
--------+---------+------------+---------
id1 | integer | id1 | plain
primary key, btree, for table "public.test"
View "public.v_test"
Column | Type | Modifiers | Storage | Description
--------+-----------------------+-----------+----------+-------------
id1 | integer | | plain |
id2 | integer | | plain |
name | character varying(10) | | extended |
View definition:
SELECT *
FROM test;
enmo=# select proname,prosrc from pg_proc where proname like 'func_test%';
proname | prosrc
------------+----------------------------------------------------------------
func_test1 | +
| BEGIN +
| return query select count(*) from test; +
| END; +
|
func_test2 | +
| BEGIN +
| return query select count(*) from public.test;+
| END; +
|
(2 rows)
enmo=# select *from test;
id1 | id2 | name
-----+-----+-------
1 | 1 | test1
(1 row)
#表test改名为ttt
enmo=# alter table test rename to ttt;
ALTER TABLE
#再次查看表的相关对象
enmo=# \d+ *test*
Index "public.idx_test_name"
Column | Type | Definition | Storage
--------+-----------------------+------------+----------
name | character varying(10) | name | extended
btree, for table "public.ttt"
Sequence "public.s_test_1"
Column | Type | Value | Storage
---------------+---------+---------------------+---------
sequence_name | name | s_test_1 | plain
last_value | bigint | 1 | plain
start_value | bigint | 1 | plain
increment_by | bigint | 1 | plain
max_value | bigint | 9223372036854775807 | plain
min_value | bigint | 1 | plain
cache_value | bigint | 1 | plain
log_cnt | bigint | 32 | plain
is_cycled | boolean | f | plain
is_called | boolean | t | plain
uuid | bigint | 0 | plain
Owned by: public.ttt.id2
Sequence "public.test_id1_seq"
Column | Type | Value | Storage
---------------+---------+---------------------+---------
sequence_name | name | test_id1_seq | plain
last_value | bigint | 1 | plain
start_value | bigint | 1 | plain
increment_by | bigint | 1 | plain
max_value | bigint | 9223372036854775807 | plain
min_value | bigint | 1 | plain
cache_value | bigint | 1 | plain
log_cnt | bigint | 32 | plain
is_cycled | boolean | f | plain
is_called | boolean | t | plain
uuid | bigint | 0 | plain
Owned by: public.ttt.id1
Index "public.test_pkey"
Column | Type | Definition | Storage
--------+---------+------------+---------
id1 | integer | id1 | plain
primary key, btree, for table "public.ttt"
View "public.v_test"
Column | Type | Modifiers | Storage | Description
--------+-----------------------+-----------+----------+-------------
id1 | integer | | plain |
id2 | integer | | plain |
name | character varying(10) | | extended |
View definition:
SELECT *
FROM ttt test;
enmo=# \d+ *ttt*
Table "public.ttt"
Column | Type | Modifiers | Storage | Stats target | Description
--------+-----------------------+----------------------------------------------------+----------+--------------+-------------
id1 | integer | not null default nextval('test_id1_seq'::regclass) | plain | |
id2 | integer | | plain | |
name | character varying(10) | | extended | |
Indexes:
"test_pkey" PRIMARY KEY, btree (id1) TABLESPACE pg_default
"idx_test_name" btree (name) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no
enmo=# select proname,prosrc from pg_proc where proname like 'func_test%';
proname | prosrc
------------+----------------------------------------------------------------
func_test1 | +
| BEGIN +
| return query select count(*) from test; +
| END; +
|
func_test2 | +
| BEGIN +
| return query select count(*) from public.test;+
| END; +
|
(2 rows)
enmo=# select *from ttt;
id1 | id2 | name
-----+-----+-------
1 | 1 | test1
(1 row)
enmo=# select func_test1();
ERROR: relation "test" does not exist on dn_6001
LINE 1: select func_test1();
^
CONTEXT: PL/pgSQL function func_test1() line 3 at RETURN QUERY
referenced column: func_test1
OpenGauss数据库修改表名操作：
#修改表名前
postgres=# \d+ *test*
Index "public.idx_test_name"
Column | Type | Definition | Storage
--------+-----------------------+------------+----------
name | character varying(10) | name | extended
btree, for table "public.test"
Sequence "public.s_test_1"
Column | Type | Value | Storage
---------------+---------+---------------------+---------
sequence_name | name | s_test_1 | plain
last_value | bigint | 1 | plain
start_value | bigint | 1 | plain
increment_by | bigint | 1 | plain
max_value | bigint | 9223372036854775807 | plain
min_value | bigint | 1 | plain
cache_value | bigint | 1 | plain
log_cnt | bigint | 32 | plain
is_cycled | boolean | f | plain
is_called | boolean | t | plain
uuid | bigint | 0 | plain
Owned by: public.test.id2
Table "public.test"
Column | Type | Modifiers | Storage | Stats target | Description
--------+-----------------------+----------------------------------------------------+----------+--------------+-------------
id1 | integer | not null default nextval('test_id1_seq'::regclass) | plain | |
id2 | integer | | plain | |
name | character varying(10) | | extended | |
Indexes:
"test_pkey" PRIMARY KEY, btree (id1) TABLESPACE pg_default
"idx_test_name" btree (name) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no
Sequence "public.test_id1_seq"
Column | Type | Value | Storage
---------------+---------+---------------------+---------
sequence_name | name | test_id1_seq | plain
last_value | bigint | 1 | plain
start_value | bigint | 1 | plain
increment_by | bigint | 1 | plain
max_value | bigint | 9223372036854775807 | plain
min_value | bigint | 1 | plain
cache_value | bigint | 1 | plain
log_cnt | bigint | 32 | plain
is_cycled | boolean | f | plain
is_called | boolean | t | plain
uuid | bigint | 0 | plain
Owned by: public.test.id1
Index "public.test_pkey"
Column | Type | Definition | Storage
--------+---------+------------+---------
id1 | integer | id1 | plain
primary key, btree, for table "public.test"
View "public.v_test"
Column | Type | Modifiers | Storage | Description
--------+-----------------------+-----------+----------+-------------
id1 | integer | | plain |
id2 | integer | | plain |
name | character varying(10) | | extended |
View definition:
SELECT *
FROM test;
postgres=# select proname,prosrc from pg_proc where proname like 'func_test%';
proname | prosrc
------------+----------------------------------------------------------------
func_test1 | +
| BEGIN +
| return query select count(*) from test; +
| END; +
|
func_test2 | +
| BEGIN +
| return query select count(*) from public.test;+
| END; +
|
(2 rows)
postgres=# select *from test;
id1 | id2 | name
-----+-----+-------
1 | 1 | test1
(1 row)
postgres=# select func_test1();
func_test1
------------
1
(1 row)
postgres=#
#表test改名为ttt
postgres=# alter table test rename to ttt;
ALTER TABLE
#再次查看表的相关对象
postgres=# \d+ *test*
Index "public.idx_test_name"
Column | Type | Definition | Storage
--------+-----------------------+------------+----------
name | character varying(10) | name | extended
btree, for table "public.ttt"
Sequence "public.s_test_1"
Column | Type | Value | Storage
---------------+---------+---------------------+---------
sequence_name | name | s_test_1 | plain
last_value | bigint | 1 | plain
start_value | bigint | 1 | plain
increment_by | bigint | 1 | plain
max_value | bigint | 9223372036854775807 | plain
min_value | bigint | 1 | plain
cache_value | bigint | 1 | plain
log_cnt | bigint | 32 | plain
is_cycled | boolean | f | plain
is_called | boolean | t | plain
uuid | bigint | 0 | plain
Owned by: public.ttt.id2
Sequence "public.test_id1_seq"
Column | Type | Value | Storage
---------------+---------+---------------------+---------
sequence_name | name | test_id1_seq | plain
last_value | bigint | 1 | plain
start_value | bigint | 1 | plain
increment_by | bigint | 1 | plain
max_value | bigint | 9223372036854775807 | plain
min_value | bigint | 1 | plain
cache_value | bigint | 1 | plain
log_cnt | bigint | 32 | plain
is_cycled | boolean | f | plain
is_called | boolean | t | plain
uuid | bigint | 0 | plain
Owned by: public.ttt.id1
Index "public.test_pkey"
Column | Type | Definition | Storage
--------+---------+------------+---------
id1 | integer | id1 | plain
primary key, btree, for table "public.ttt"
View "public.v_test"
Column | Type | Modifiers | Storage | Description
--------+-----------------------+-----------+----------+-------------
id1 | integer | | plain |
id2 | integer | | plain |
name | character varying(10) | | extended |
View definition:
SELECT *
FROM ttt test;
postgres=# \d+ *ttt*
Table "public.ttt"
Column | Type | Modifiers | Storage | Stats target | Description
--------+-----------------------+----------------------------------------------------+----------+--------------+-------------
id1 | integer | not null default nextval('test_id1_seq'::regclass) | plain | |
id2 | integer | | plain | |
name | character varying(10) | | extended | |
Indexes:
"test_pkey" PRIMARY KEY, btree (id1) TABLESPACE pg_default
"idx_test_name" btree (name) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no
postgres=# select proname,prosrc from pg_proc where proname like 'func_test%';
proname | prosrc
------------+----------------------------------------------------------------
func_test1 | +
| BEGIN +
| return query select count(*) from test; +
| END; +
|
func_test2 | +
| BEGIN +
| return query select count(*) from public.test;+
| END; +
|
(2 rows)
postgres=# select func_test1();
ERROR: relation "test" does not exist on sgnode
LINE 1: select func_test1();
^
CONTEXT: PL/pgSQL function func_test1() line 3 at RETURN QUERY
referenced column: func_test1
postgres=# select *from ttt;
id1 | id2 | name
-----+-----+-------
1 | 1 | test1
(1 row)
PosgreSQL数据库修改表名操作：
#修改表名前
postgres=# \d+ public.*test*
Index "public.idx_test_name"
Column | Type | Key? | Definition | Storage | Stats target
--------+-----------------------+------+------------+----------+--------------
name | character varying(10) | yes | name | extended |
btree, for table "public.test"
Sequence "public.s_test_1"
Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
--------+-------+---------+---------------------+-----------+---------+-------
bigint | 1 | 1 | 9223372036854775807 | 1 | no | 1
Owned by: public.test.id2
Table "public.test"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------+-----------+----------+-----------------------------------+----------+--------------+-------------
id1 | integer | | not null | nextval('test_id1_seq'::regclass) | plain | |
id2 | integer | | | | plain | |
name | character varying(10) | | | | extended | |
Indexes:
"test_pkey" PRIMARY KEY, btree (id1)
"idx_test_name" btree (name)
Access method: heap
Sequence "public.test_id1_seq"
Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
---------+-------+---------+------------+-----------+---------+-------
integer | 1 | 1 | 2147483647 | 1 | no | 1
Owned by: public.test.id1
Index "public.test_pkey"
Column | Type | Key? | Definition | Storage | Stats target
--------+---------+------+------------+---------+--------------
id1 | integer | yes | id1 | plain |
primary key, btree, for table "public.test"
View "public.v_test"
Column | Type | Collation | Nullable | Default | Storage | Description
--------+-----------------------+-----------+----------+---------+----------+-------------
id1 | integer | | | | plain |
id2 | integer | | | | plain |
name | character varying(10) | | | | extended |
View definition:
SELECT test.id1,
test.id2,
test.name
FROM test;
postgres=# select proname,prosrc from pg_proc where proname like 'func_test%';
proname | prosrc
------------+----------------------------------------------------------------
func_test1 | +
| BEGIN +
| return query select count(*) from test; +
| END; +
|
func_test2 | +
| BEGIN +
| return query select count(*) from public.test;+
| END; +
|
(2 rows)
postgres=# select *from test;
id1 | id2 | name
-----+-----+-------
1 | 1 | test1
(1 row)
postgres=# select func_test1();
func_test1
------------
1
(1 row)
postgres=#
#表test改名为ttt
postgres=# alter table test rename to ttt;
ALTER TABLE
#再次查看表的相关对象
postgres=# \d+ public.*test*
Index "public.idx_test_name"
Column | Type | Key? | Definition | Storage | Stats target
--------+-----------------------+------+------------+----------+--------------
name | character varying(10) | yes | name | extended |
btree, for table "public.ttt"
Sequence "public.s_test_1"
Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
--------+-------+---------+---------------------+-----------+---------+-------
bigint | 1 | 1 | 9223372036854775807 | 1 | no | 1
Owned by: public.ttt.id2
Sequence "public.test_id1_seq"
Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
---------+-------+---------+------------+-----------+---------+-------
integer | 1 | 1 | 2147483647 | 1 | no | 1
Owned by: public.ttt.id1
Index "public.test_pkey"
Column | Type | Key? | Definition | Storage | Stats target
--------+---------+------+------------+---------+--------------
id1 | integer | yes | id1 | plain |
primary key, btree, for table "public.ttt"
View "public.v_test"
Column | Type | Collation | Nullable | Default | Storage | Description
--------+-----------------------+-----------+----------+---------+----------+-------------
id1 | integer | | | | plain |
id2 | integer | | | | plain |
name | character varying(10) | | | | extended |
View definition:
SELECT ttt.id1,
ttt.id2,
ttt.name
FROM ttt;
postgres=# \d+ public.*ttt*
Table "public.ttt"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------+-----------+----------+-----------------------------------+----------+--------------+-------------
id1 | integer | | not null | nextval('test_id1_seq'::regclass) | plain | |
id2 | integer | | | | plain | |
name | character varying(10) | | | | extended | |
Indexes:
"test_pkey" PRIMARY KEY, btree (id1)
"idx_test_name" btree (name)
Access method: heap
postgres=# select proname,prosrc from pg_proc where proname like 'func_test%';
proname | prosrc
------------+----------------------------------------------------------------
func_test1 | +
| BEGIN +
| return query select count(*) from test; +
| END; +
|
func_test2 | +
| BEGIN +
| return query select count(*) from public.test;+
| END; +
|
(2 rows)
postgres=# select *from test;
ERROR: relation "test" does not exist
LINE 1: select *from test;
^
postgres=# select *from ttt;
id1 | id2 | name
-----+-----+-------
1 | 1 | test1
(1 row)
postgres=# select proname,prosrc from pg_proc where proname like 'func_test%';
proname | prosrc
------------+----------------------------------------------------------------
func_test1 | +
| BEGIN +
| return query select count(*) from test; +
| END; +
|
func_test2 | +
| BEGIN +
| return query select count(*) from public.test;+
| END; +
|
(2 rows)
postgres=#
结论：
修改表名之后，数据库会自动更新它的依赖对象，比如索引、约束和视图，但是不会更新与表相关存储过程的内容，需要手动调整存储过程。
修改表的所属模式
在MogDB/OpenGauss/Postgres数据库中同样可以通过alter table 命令来修改表的所属模式：
alter table 表名 set schema 新模式名称；
我们继续测试一下，修改表的所属schema后，与原表相关联的那些数据库对象状态会有什么变化？
首先，继续创建测试表及相关对象（比如：索引，序列，视图，函数）。
MogDB/OpenGauss/PostgreSQL数据库，分别进行如下操作：
create table aaa (id1 int PRIMARY KEY,id2 int,name varchar(10));
create index idx_aaa_name on aaa(name);
create view v_aaa as select * from aaa;
CREATE OR REPLACE FUNCTION func_aaa1() RETURNS setof bigint AS $$
BEGIN
return query select count(*) from aaa;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION func_aaa2() RETURNS setof bigint AS $$
BEGIN
return query select count(*) from public.aaa;
END;
$$ LANGUAGE plpgsql;
insert into aaa(id1,id2,name) values(1,1,'aaa');
create schema omm2;
MogDB数据库修改表的所属schema操作：
#依赖对象中有序列的表，修改所属模式会失败：
enmo=# \d+ *ttt*
Table "public.ttt"
Column | Type | Modifiers | Storage | Stats target | Description
--------+-----------------------+----------------------------------------------------+----------+--------------+-------------
id1 | integer | not null default nextval('test_id1_seq'::regclass) | plain | |
id2 | integer | | plain | |
name | character varying(10) | | extended | |
Indexes:
"test_pkey" PRIMARY KEY, btree (id1) TABLESPACE pg_default
"idx_test_name" btree (name) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no
enmo=# alter table ttt set schema omm2;
ERROR: There's dependent sequence, but ALTER SEQUENCE SET SCHEMA is not yet supported.
#修改表的所属模式前
enmo=# \d+ *aaa*
Table "public.aaa"
Column | Type | Modifiers | Storage | Stats target | Description
--------+-----------------------+-----------+----------+--------------+-------------
id1 | integer | not null | plain | |
id2 | integer | | plain | |
name | character varying(10) | | extended | |
Indexes:
"aaa_pkey" PRIMARY KEY, btree (id1) TABLESPACE pg_default
"idx_aaa_name" btree (name) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no
Index "public.aaa_pkey"
Column | Type | Definition | Storage
--------+---------+------------+---------
id1 | integer | id1 | plain
primary key, btree, for table "public.aaa"
Index "public.idx_aaa_name"
Column | Type | Definition | Storage
--------+-----------------------+------------+----------
name | character varying(10) | name | extended
btree, for table "public.aaa"
View "public.v_aaa"
Column | Type | Modifiers | Storage | Description
--------+-----------------------+-----------+----------+-------------
id1 | integer | | plain |
id2 | integer | | plain |
name | character varying(10) | | extended |
View definition:
SELECT *
FROM aaa;
enmo=# select proname,prosrc from pg_proc where proname like 'func_aaa%';
proname | prosrc
-----------+---------------------------------------------------------------
func_aaa1 | +
| BEGIN +
| return query select count(*) from aaa; +
| END; +
|
func_aaa2 | +
| BEGIN +
| return query select count(*) from public.aaa;+
| END; +
|
(2 rows)
enmo=# \d+ omm2.*aaa*
Did not find any relation named "omm2.*aaa*".
#修改表的所属模式
enmo=# alter table aaa set schema omm2;
ALTER TABLE
#再次查看表的相关对象
enmo=# \d+ *aaa*
View "public.v_aaa"
Column | Type | Modifiers | Storage | Description
--------+-----------------------+-----------+----------+-------------
id1 | integer | | plain |
id2 | integer | | plain |
name | character varying(10) | | extended |
View definition:
SELECT *
FROM omm2.aaa;
enmo=# \d+ omm2.*aaa*
Table "omm2.aaa"
Column | Type | Modifiers | Storage | Stats target | Description
--------+-----------------------+-----------+----------+--------------+-------------
id1 | integer | not null | plain | |
id2 | integer | | plain | |
name | character varying(10) | | extended | |
Indexes:
"aaa_pkey" PRIMARY KEY, btree (id1) TABLESPACE pg_default
"idx_aaa_name" btree (name) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no
Index "omm2.aaa_pkey"
Column | Type | Definition | Storage
--------+---------+------------+---------
id1 | integer | id1 | plain
primary key, btree, for table "omm2.aaa"
Index "omm2.idx_aaa_name"
Column | Type | Definition | Storage
--------+-----------------------+------------+----------
name | character varying(10) | name | extended
btree, for table "omm2.aaa"
enmo=# select proname,prosrc from pg_proc where proname like 'func_aaa%';
proname | prosrc
-----------+---------------------------------------------------------------
func_aaa1 | +
| BEGIN +
| return query select count(*) from aaa; +
| END; +
|
func_aaa2 | +
| BEGIN +
| return query select count(*) from public.aaa;+
| END; +
|
(2 rows)
OpenGauss数据库修改表的所属schema操作：
#依赖对象中有序列的表，修改所属模式会失败：
postgres=# \d+ *ttt*
Table "public.ttt"
Column | Type | Modifiers | Storage | Stats target | Description
--------+-----------------------+----------------------------------------------------+----------+--------------+-------------
id1 | integer | not null default nextval('test_id1_seq'::regclass) | plain | |
id2 | integer | | plain | |
name | character varying(10) | | extended | |
Indexes:
"test_pkey" PRIMARY KEY, btree (id1) TABLESPACE pg_default
"idx_test_name" btree (name) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no
postgres=# alter table ttt set schema omm2;
ERROR: There's dependent sequence, but ALTER SEQUENCE SET SCHEMA is not yet supported.
#修改表的所属模式前
postgres=# \d+ public.*aaa*
Table "public.aaa"
Column | Type | Modifiers | Storage | Stats target | Description
--------+-----------------------+-----------+----------+--------------+-------------
id1 | integer | not null | plain | |
id2 | integer | | plain | |
name | character varying(10) | | extended | |
Indexes:
"aaa_pkey" PRIMARY KEY, btree (id1) TABLESPACE pg_default
"idx_aaa_name" btree (name) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no
Index "public.aaa_pkey"
Column | Type | Definition | Storage
--------+---------+------------+---------
id1 | integer | id1 | plain
primary key, btree, for table "public.aaa"
Index "public.idx_aaa_name"
Column | Type | Definition | Storage
--------+-----------------------+------------+----------
name | character varying(10) | name | extended
btree, for table "public.aaa"
View "public.v_aaa"
Column | Type | Modifiers | Storage | Description
--------+-----------------------+-----------+----------+-------------
id1 | integer | | plain |
id2 | integer | | plain |
name | character varying(10) | | extended |
View definition:
SELECT *
FROM aaa;
postgres=# \d+ omm2.*aaa*
Did not find any relation named "omm2.*aaa*".
postgres=# select proname,prosrc from pg_proc where proname like 'func_aaa%';
proname | prosrc
-----------+---------------------------------------------------------------
func_aaa1 | +
| BEGIN +
| return query select count(*) from aaa; +
| END; +
|
func_aaa2 | +
| BEGIN +
| return query select count(*) from public.aaa;+
| END; +
|
(2 rows)
postgres=#
#修改表的所属模式
postgres=# alter table aaa set schema omm2;
ALTER TABLE
#再次查看表的相关对象
postgres=# \d+ public.*aaa*
View "public.v_aaa"
Column | Type | Modifiers | Storage | Description
--------+-----------------------+-----------+----------+-------------
id1 | integer | | plain |
id2 | integer | | plain |
name | character varying(10) | | extended |
View definition:
SELECT *
FROM omm2.aaa;
postgres=# \d+ omm2.*aaa*
Table "omm2.aaa"
Column | Type | Modifiers | Storage | Stats target | Description
--------+-----------------------+-----------+----------+--------------+-------------
id1 | integer | not null | plain | |
id2 | integer | | plain | |
name | character varying(10) | | extended | |
Indexes:
"aaa_pkey" PRIMARY KEY, btree (id1) TABLESPACE pg_default
"idx_aaa_name" btree (name) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no
Index "omm2.aaa_pkey"
Column | Type | Definition | Storage
--------+---------+------------+---------
id1 | integer | id1 | plain
primary key, btree, for table "omm2.aaa"
Index "omm2.idx_aaa_name"
Column | Type | Definition | Storage
--------+-----------------------+------------+----------
name | character varying(10) | name | extended
btree, for table "omm2.aaa"
postgres=# select proname,prosrc from pg_proc where proname like 'func_aaa%';
proname | prosrc
-----------+---------------------------------------------------------------
func_aaa1 | +
| BEGIN +
| return query select count(*) from aaa; +
| END; +
|
func_aaa2 | +
| BEGIN +
| return query select count(*) from public.aaa;+
| END; +
|
(2 rows)
postgres=# select * from omm2.aaa;
id1 | id2 | name
-----+-----+------
1 | 1 | aaa
(1 row)
postgres=# select func_aaa1();
ERROR: relation "aaa" does not exist on sgnode
LINE 1: select count(*)from aaa
^
QUERY: select count(*)from aaa
CONTEXT: PL/pgSQL function func_aaa1() line 3 at RETURN QUERY
referenced column: func_aaa1
postgres=#
PosgreSQL数据库修改表的所属schema操作：
#修改表的所属模式前
postgres=# \d+ *ttt*
Table "public.ttt"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------+-----------+----------+-----------------------------------+----------+--------------+-------------
id1 | integer | | not null | nextval('test_id1_seq'::regclass) | plain | |
id2 | integer | | | | plain | |
name | character varying(10) | | | | extended | |
Indexes:
"test_pkey" PRIMARY KEY, btree (id1)
"idx_test_name" btree (name)
Access method: heap
#修改表的所属模式
postgres=# alter table ttt set schema omm2;
ALTER TABLE
#再次查看表的相关对象
postgres=# \d+ *ttt*
Did not find any relation named "*ttt*".
postgres=# \d+ omm2.*ttt*
Table "omm2.ttt"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------+-----------+----------+----------------------------------------+----------+--------------+-------------
id1 | integer | | not null | nextval('omm2.test_id1_seq'::regclass) | plain | |
id2 | integer | | | | plain | |
name | character varying(10) | | | | extended | |
Indexes:
"test_pkey" PRIMARY KEY, btree (id1)
"idx_test_name" btree (name)
Access method: heap
postgres=# \d+ public.*ttt*
Did not find any relation named "public.*ttt*".
postgres=# \d+ public.*test*
View "public.v_test"
Column | Type | Collation | Nullable | Default | Storage | Description
--------+-----------------------+-----------+----------+---------+----------+-------------
id1 | integer | | | | plain |
id2 | integer | | | | plain |
name | character varying(10) | | | | extended |
View definition:
SELECT ttt.id1,
ttt.id2,
ttt.name
FROM omm2.ttt;
postgres=# \d+ omm2.*test*
Index "omm2.idx_test_name"
Column | Type | Key? | Definition | Storage | Stats target
--------+-----------------------+------+------------+----------+--------------
name | character varying(10) | yes | name | extended |
btree, for table "omm2.ttt"
Sequence "omm2.s_test_1"
Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
--------+-------+---------+---------------------+-----------+---------+-------
bigint | 1 | 1 | 9223372036854775807 | 1 | no | 1
Owned by: omm2.ttt.id2
Sequence "omm2.test_id1_seq"
Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
---------+-------+---------+------------+-----------+---------+-------
integer | 1 | 1 | 2147483647 | 1 | no | 1
Owned by: omm2.ttt.id1
Index "omm2.test_pkey"
Column | Type | Key? | Definition | Storage | Stats target
--------+---------+------+------------+---------+--------------
id1 | integer | yes | id1 | plain |
primary key, btree, for table "omm2.ttt"
postgres=# select *from omm2.ttt;
id1 | id2 | name
-----+-----+-------
1 | 1 | test1
(1 row)
postgres=# select proname,prosrc from pg_proc where proname like 'func_test%';
proname | prosrc
------------+----------------------------------------------------------------
func_test1 | +
| BEGIN +
| return query select count(*) from test; +
| END; +
|
func_test2 | +
| BEGIN +
| return query select count(*) from public.test;+
| END; +
|
(2 rows)
postgres=#
结论：
MogDB/OpenGauss数据库：
如果对于存在依赖该表的序列，修改表的所属模式会失败。
成功修改表的所属模式之后，依赖表的约束和索引的所属模式也会自动做相应的调整。视图的所属模式不会被调整，但是视图定义中表的所属模式会自动做相应的调整。表相关存储过程的内容不会自动调整，需要手动调整存储过程。
PostgreSQL 数据库：
成功修改表的所属模式之后，依赖表的约束和索引以及序列的所属模式也会自动做相应的调整。视图的所属模式不会被调整，但是视图定义中表的所属模式会自动做相应的调整。表相关存储过程的内容不会自动调整，需要手动调整存储过程。
最后修改时间：2022-01-25 16:43:35
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
