磐维数据库如何屏蔽关键字以适配国产化应用系统

背景

      某应用系统国产化改造,因SQL写在了代码里面，要改设计的话，流程比较繁琐，所以想通过屏蔽磐维关键字,使指定的关键字可做为别名或字段名

操作步骤:

1.启动数据库

su - omm

gs_ctl start -D /database/panweidb/data

2.查看版本

[omm@pwnode1 ~]$ gsql -r
gsql ((PanWeiDB_V2.0-S2.0.3_B01) compiled at 2024-05-25 22:36:36 commit e857234 )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

postgres=# select pw_version();
                                 pw_version                                  
-----------------------------------------------------------------------------
 (PanWeiDB_V2.0-S2.0.3_B01) compiled at 2024-05-25 22:36:36 commit e857234  +
  product name:PanWeiDB                                                     +
  version:V2.0-S2.0.3_B01                                                   +
  commit:e857234                                                            +
  openGauss version:5.0.0                                                   +
  host:x86_64-pc-linux-gnu
(1 row)

postgres=#

3.识别 “关键字” 是否可为字段名或别名

[omm@pwnode1 ~]$ gsql -r
gsql ((PanWeiDB_V2.0-S2.0.3_B01) compiled at 2024-05-25 22:36:36 commit e857234 )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

postgres=# select 1 REJECT;
 reject 
--------
      1
(1 row)

postgres=# select 1 OUTPUT;
ERROR:  syntax error at or near "OUTPUT;"
LINE 1: select 1 OUTPUT;
                 ^
postgres=# select 1 "OUTPUT";
 OUTPUT 
--------
      1
(1 row)

postgres=# create table t1(REJECT int);
ERROR:  syntax error at or near "REJECT"
LINE 1: create table t1(REJECT int);
                        ^
postgres=# create table t1(OUTPUT int);
ERROR:  syntax error at or near "OUTPUT int"
LINE 1: create table t1(OUTPUT int);
                        ^
postgres=# 

注:

保留字不能用做其他标识符，非保留字只是在特定的环境里有特殊的含义，而在其他环境里是可以用做标识符的

REJECT 可以直接做为别名,但不可做为字段名,OUTPUT即不可做别名，也不能做字段名

4.查询pw_exclude_reserved_words参数值

postgres=# show pw_exclude_reserved_words;
 pw_exclude_reserved_words 
---------------------------
 
(1 row)

postgres=#

5.设置pw_exclude_reserved_words

[omm@pwnode1 ~]$ gs_guc reload  -I all -N all -c "pw_exclude_reserved_words='REJECT,OUTPUT'" 
The pw_guc run with the following arguments: [gs_guc -I all -N all -c pw_exclude_reserved_words='REJECT,OUTPUT' reload ].
Begin to perform the total nodes: 1.
Popen count is 1, Popen success count is 1, Popen failure count is 0.
Begin to perform gs_guc for datanodes.
Command count is 1, Command success count is 1, Command failure count is 0.

Total instances: 1. Failed instances: 0.
ALL: Success to perform gs_guc!


[omm@pwnode1 ~]$ gs_ctl restart -D /database/panweidb/data

6.检查pw_exclude_reserved_words参数值

[omm@pwnode1 ~]$ gsql -r
gsql ((PanWeiDB_V2.0-S2.0.3_B01) compiled at 2024-05-25 22:36:36 commit e857234 )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

postgres=# show pw_exclude_reserved_words;
 pw_exclude_reserved_words 
---------------------------
 REJECT,OUTPUT
(1 row)

postgres=#

7.验证结果

postgres=# show pw_exclude_reserved_words;
 pw_exclude_reserved_words 
---------------------------
 REJECT,OUTPUT
(1 row)

postgres=# select 1 REJECT;
 reject 
--------
      1
(1 row)

postgres=# select 1 OUTPUT;
 output 
--------
      1
(1 row)

postgres=# create table tt1(REJECT int);
CREATE TABLE
postgres=# create table tt2(OUTPUT int);
CREATE TABLE
postgres=#