0. 前言
作为一个习惯于使用终端进行运维的DBA, 手上肯定需要一批SQL脚本，用于日常的运维和优化工作。这些脚本中，往往需要用户输入一些变量。
相对上一个版本，传字符串形式有更新，更方便了=
那么，在MogDB/OpenGauss中，如何执行脚本？如何给脚本传递变量呢？
1. 执行不带变量的SQL脚本
不带变量的SQL脚本执行相对比较简单。我们分为两种情况讨论。一种是当前输入界面已经在gsql中，去调用SQL脚本；另一种是在SHELL里面，命令行调用gsql并给它传递SQL脚本。
1.1 准备一个测试用的SQL脚本（不含变量）
cat > sess.sql <<EOF
select pid,usename,application_name,xact_start,query_start,waiting,state
from pg_stat_activity
where state = 'active';
EOF
1.2 在gsql里面调用SQL脚本： \i 脚本文件名
gsql提供一个元命令，\i , 用于调用SQL脚本。
比如：
gsql -r \i sess.sql
pid | usename | application_name | xact_start | query_start | waiting | state
----------------+---------+------------------+-------------------------------+-------------------------------+---------+--------
22872621315840 | tpcc | gsql | 2024-07-10 15:40:21.179175+08 | 2024-07-10 15:40:21.179175+08 | f | active
22846861993728 | tpcc | JobScheduler | | | f | active
\i 有几个变种，其中 \ir 是用于SQL脚本中嵌套 \i 时，使用的路径是以被调用的SQL脚本为当前路径的。而 \i+ 和 \ir+ 则是允许脚本进行加密。关于脚本嵌套和脚本加密，这里就不展开了。
1.3 在Shell环境执行gsql + SQL脚本
在Shell环境执行gsql + SQL脚本, 则有三种用法：
1.3.1 标准用法： -f 脚本文件名
最标准用法是使用gsql的命令行选项 -f, 比如
gsql -f sess.sql
pid | usename | application_name | xact_start | query_start | waiting | state
----------------+---------+------------------+-------------------------------+-------------------------------+---------+--------
22873709213440 | tpcc | gsql | 2024-07-10 15:49:29.524565+08 | 2024-07-10 15:49:29.524565+08 | f | active
22846861993728 | tpcc | JobScheduler | | | f | active
1.3.2 SHELL输入重定向：< 脚本文件名
另一种用法是把 SQL脚本中的内容模仿标准输入，传给gsql命令，也就是使用 输入重定向符号 < + 脚本文件名
如：
gsql < sess.sql
pid | usename | application_name | xact_start | query_start | waiting | state
----------------+---------+------------------+-------------------------------+-------------------------------+---------+--------
22873349551872 | tpcc | gsql | 2024-07-10 15:56:06.868991+08 | 2024-07-10 15:56:06.868991+08 | f | active
22846861993728 | tpcc | JobScheduler | | | f | active
1.3.3 利用gsql -c 选项：-c “\i 脚本文件名”
这个其实利用gsql里面的\i元命令，其实是绕了一圈，用这种方法应该比较少。
比如:
gsql -c "\i sess.sql"
pid | usename | application_name | xact_start | query_start | waiting | state
----------------+---------+------------------+-------------------------------+-------------------------------+---------+--------
22872307726080 | tpcc | gsql | 2024-07-10 16:00:04.077213+08 | 2024-07-10 16:00:04.077213+08 | f | active
22846861993728 | tpcc | JobScheduler | | | f | active
2. 执行带变量的SQL脚本
调用不带变量的SQL脚本还是比较简单的。下面进入困难模式，调用的SQL需要特定的变量。比如，刚才那个示例SQL脚本，输出的是status=‘active’, 可是，如果希望根据变量来输出不同status的结果，就会相对麻烦很多。
2.1 gsql中的变量使用
幸运的是，gsql的确是支持变量的。内部称为"Variables"。
变量的设置有几种方式，分别是:
-
gsql命令行选项: -v 变量名称=变量值
-
gsql内赋值: \set 变量名称 变量值
-
gsql内交互式输入: \prompt 变量名称
而调用的时候，使用 :变量名 形式进行调用。但是，这里面有个非常重要的点，:变量名 不能放在引号里面，或者说，:变量名 不能用引号括起来，因为对于gsql而言，无法判断引号里的冒号是字符串内容的一部分还是变量名的前缀，所以，统一当做字符串内容的一部分来处理。
这个处理逻辑和Oracle不一样，Oracle使用 & 符号来作为变量的前缀，在sqlplus里set define on（默认选项）时，无论&符号是否被放在引号里面，都会进行替换。
2.2 gsql中的数字类型变量使用
如果需要传入的字符串，这个问题处理起来会很复杂，因此，我们循序渐进，先通过相对简单的传入数字的形式来熟悉变量的使用。
假设现在我们需要检查数据库中空间大小大于x MB的对象。
SQL类似于这样:
cat > relsize.sql <<EOF
select relname,relpages*8192/1024/1024 relsize
from pg_class
where relpages * 8192/1024/1024 > :size
order by relsize desc;
EOF
2.2.1 gsql命令行选项: -v 变量名称=变量值
从SHELL中调用
检查大于10MB的
gsql -v size=10 -f relsize.sql
relname | relsize
-----------------------------------------+------------
snap_global_stat_all_indexes | 45.5546875
snap_global_statio_all_indexes | 41.8125
snap_class_vital_info | 40.25
snap_global_config_settings | 35.65625
snap_global_stat_all_tables | 33.25
snap_summary_stat_all_indexes | 32.4453125
snap_summary_statio_all_indexes | 31.7265625
snap_global_statio_all_tables | 26.1953125
snap_summary_stat_all_tables | 21.34375
snap_summary_statio_all_tables | 17.9921875
snap_summary_transactions_running_xacts | 16.5703125
snap_global_transactions_running_xacts | 16.4921875
snap_global_thread_wait_status | 16.3828125
pg_toast_2618 | 13.6875
snap_global_os_threads | 11.3359375
(15 rows)
检查大于30MB的
gsql -f relsize.sql -v size=30
relname | relsize
---------------------------------+------------
snap_global_stat_all_indexes | 45.5546875
snap_global_statio_all_indexes | 41.8125
snap_class_vital_info | 40.25
snap_global_config_settings | 35.65625
snap_global_stat_all_tables | 33.25
snap_summary_stat_all_indexes | 32.4453125
snap_summary_statio_all_indexes | 31.7265625
2.2.2 gsql内元命令: \set 变量名称 变量值
从gsql中调用，需先设置变量值
检查大于10MB的
gsql
\set size 10
\i relsize.sql
relname | relsize
-----------------------------------------+------------
snap_global_stat_all_indexes | 45.5546875
snap_global_statio_all_indexes | 41.8125
snap_class_vital_info | 40.25
snap_global_config_settings | 35.65625
snap_global_stat_all_tables | 33.25
snap_summary_stat_all_indexes | 32.4453125
snap_summary_statio_all_indexes | 31.7265625
snap_global_statio_all_tables | 26.1953125
snap_summary_stat_all_tables | 21.34375
snap_summary_statio_all_tables | 17.9921875
snap_summary_transactions_running_xacts | 16.5703125
snap_global_transactions_running_xacts | 16.4921875
snap_global_thread_wait_status | 16.3828125
pg_toast_2618 | 13.6875
snap_global_os_threads | 11.3359375
(15 rows)
30MB同理，不占用篇幅。
2.2.3 gsql内元命令等待输入: \prompt 变量名称
检查大于30MB的
gsql \prompt size 30 \i relsize.sql
relname | relsize
---------------------------------+------------
snap_global_stat_all_indexes | 45.5546875
snap_global_statio_all_indexes | 41.8125
snap_class_vital_info | 40.25
snap_global_config_settings | 35.65625
snap_global_stat_all_tables | 33.25
snap_summary_stat_all_indexes | 32.4453125
snap_summary_statio_all_indexes | 31.7265625
(7 rows)
10MB同理，不占用篇幅。
2.2.4 让脚本包含输入提示: \prompt 变量名称
除了以上3种，其实，还可以在SQL脚本中包含输入提示部分。
脚本可以写成(给个新的名字relsize_p.sql)
cat > relsize_p.sql <<EOF
\echo Please input value of size:
\prompt size
select relname,relpages*8192/1024/1024 relsize
from pg_class
where relpages * 8192/1024/1024 > :size
order by relsize desc;
EOF
然后可以SHELL调用 gsql
gsql -f relsize_p.sql
Please input value of size:
40
relname | relsize
--------------------------------+------------
snap_global_stat_all_indexes | 45.5546875
snap_global_statio_all_indexes | 41.8125
snap_class_vital_info | 40.25
(3 rows)
或者在gsql中调用该SQL
gsql
\i relsize_p.sql
MogDB=# \i relsize_p.sql
Please input value of size:
41
relname | relsize
--------------------------------+------------
snap_global_stat_all_indexes | 45.5546875
snap_global_statio_all_indexes | 41.8125
(2 rows)
之所以特意把脚本改了个名字，是因为这是两种不同的思路，relsize.sql是可以SHELL直接后台调用，无需交互的；而relsize_p.sql是强制交互的。
如果大家有需要准备常用带变量的脚本，建议准备两套，交互或者不交互。
2.3 gsql中的字符串类型变量使用
字符串类型变量使用会复杂一些。
2.3.1 字符串变量的限制
在gsql的体系里面，变量是无法被引号括起来，下面做个不成功的尝试。
假设，我们需要检查某个数据库参数的值。计划使用类似这样的SQL进行查询
select name,setting
from pg_settings
where name ~ '参数名称'
order by name;
计划使用变量，按照Oracle的习惯，带变量的SQL会写成
select name,setting
from pg_settings
where name ~ ':name'
order by name;
尝试在gsql中跑一下
gsql -r
\set name index
select name,setting
from pg_settings
where name ~ ':name'
order by name;
返回的0行
name | setting
------+---------
(0 rows)
显然不符合要求。
这就是我前面的所说的：
变量是无法被引号括起来的
标准做法是先把不包括:的变量部分用单引号括起来。
尝试这样写
gsql -r
\set name 'index'
\echo :name
select name,setting
from pg_settings
where name ~ :'name'
order by name;
name | setting
----------------------------------+---------
cost_weight_index | 1
cpu_index_tuple_cost | 0.005
enable_default_index_compression | off
enable_hypo_index | off
enable_index_nestloop | on
enable_indexonlyscan | on
enable_indexscan | on
enable_indexscan_optimization | off
ignore_system_indexes | off
max_index_keys | 32
(10 rows)
2.3.3 通过\prompt输入时的正确用法
\prompt一个道理
\prompt name
index
select name,setting
from pg_settings
where name ~ :'name'
order by name;
成功
name | setting
----------------------------------+---------
cost_weight_index | 1
cpu_index_tuple_cost | 0.005
enable_default_index_compression | off
enable_hypo_index | off
enable_index_nestloop | on
enable_indexonlyscan | on
enable_indexscan | on
enable_indexscan_optimization | off
ignore_system_indexes | off
max_index_keys | 32
(10 rows)
2.3.4 命令行 调用 gsql -v的正确用法
类似
cat > para.sql <<EOF
select name,setting
from pg_settings
where name ~ :'name'
order by name;
EOF
gsql -v name=index -f para.sql
name | setting
----------------------------------+---------
cost_weight_index | 1
cpu_index_tuple_cost | 0.005
enable_default_index_compression | off
enable_hypo_index | off
enable_index_nestloop | on
enable_indexonlyscan | on
enable_indexscan | on
enable_indexscan_optimization | off
ignore_system_indexes | off
max_index_keys | 32
(10 rows)
没问题。
3. 总结
总结一下：
-
gsql调用SQL脚本有三种方法
> gsql输入界面中 \i 文件名
> SHELL中 gsql -f 文件名
> SHELL中 gsql -c "\i 文件名" -
SQL中变量符号为 :变量名
-
传递变量有三种方法
> gsql输入界面中 \set 变量名 变量值
> SHELL中 gsql -v 变量名=变量值
> SQL中或者gsql中使用 \prmopt 变量名进行交互式输入 -
字符串值变量使用
> 不能 在SQL中直接用单引号括起 :变量名
> 使用 :'变量名’形式来标识传入变量为字符串
- 是否交互性输入是两种不同的用法，建议使用不同的脚本文件
附注：没在PostgreSQL的psql中测试过，也许做法类似，知道的朋友可以留言告知一下
