GaussDB(DWS) 客户端gsql功能增强之动态变量

概述

为了简化数据库SQL脚本的开发模式和执行环境，数据库一般会把数据库交互的功能封装在客户端工具中，用以增加SQL脚本的灵活性，提高脚本的开发效率。

GaussDB(DWS)数据库数据库客户端工具gsql的增强特性包含动态变量、逻辑控制、大小比较、循环体等。通过这些增强特性可以实现流程式的SQL开发，提升数据仓库的SQL作业开发效率。

本篇博文主要介绍gsql的动态变量特性。

变量生成

变量生成主要有三种方式：

1. gsql命令行参数-v传递。
2. gsql元命令\set设置一个变量。
3. 执行SQL语句生成变量。

约束：

1. 变量只是简单的名称/值对。
2. 变量无类型，所有值都使用字符串存储，字符串的长度由特殊变量VAR_MAX_LENGTH决定，默认为4096。
3. 变量名只能包含大写字母、小写字母、数字和下划线。
4. 变量名区分大小写。
   

命令行参数传递：-v key=value，例如gsql -p 8109 -d postgres -v StudentId=1 -r连接数据库后会生成一个名为StudentId的变量，赋值为“1”。

postgres=# \echo :StudentId
1
postgres=# \q

元命令\set设置一个变量：\set key value，例如\set StudentId 1，表示设置一个名为StudentId的变量，赋值为“1”。

postgres=# \echo :StudentId
:StudentId
postgres=# \set StudentId 1
postgres=# \echo :StudentId
1

执行SQL生成一个变量：例如 select 1 as "StudentId"，该SQL语句成功执行之后可生成一个名为StudentId的变量，赋值为“1”。

1. 在使用命令行参数--dynamic-param或设置特殊变量DYNAMIC_PARAM_ENABLE为true时，可通过执行SQL语句设置变量，可使用${}的方式引用变量。
2. 变量名为SQL执行结果的列名。SELECT语句中的列名使用双引号区分大小写，否则统一按照小写处理。
   1. SQL执行失败时，不设置变量。
   2. SQL执行结果为空，以列名设置变量，赋值空字符串。
   3. SQL执行结果为一条记录，以列名设置变量，赋值对应字符串。
      

      postgres=# \echo ${StudentId}
      ${StudentId}
      postgres=# select 1 as "StudentId";
       StudentId
      -----------
               1
      (1 row)
      
      postgres=# \echo ${StudentId}
      1
      postgres=# \q

   4. SQL执行结果为多条记录，以列名设置变量，使用特定字符串拼接，然后赋值；特定字符串由特殊变量RESULT_DELIMITER控制，默认为逗号。
      

      postgres=# \set DYNAMIC_PARAM_ENABLE true
      postgres=# create table student (id int, name varchar(32)) distribute by hash(id);
      CREATE TABLE
      postgres=# insert into student values (1, 'Jack'), (2, 'Rose');
      INSERT 0 2
      postgres=# select * from student;
       id | name
      ----+------
        1 | Jack
        2 | Rose
      (2 rows)
      
      postgres=# \echo ${id}
      1,2
      postgres=# \echo ${name}
      Jack,Rose
      postgres=# \set RESULT_DELIMITER |
      postgres=# select * from student;
       id | name
      ----+------
        1 | Jack
        2 | Rose
      (2 rows)
      
      postgres=# \echo ${name}
      Jack|Rose

变量引用

引用变量的两种方式：

1. 在变量前加冒号。
2. 在使用命令行参数--dynamic-param或设置特殊变量DYNAMIC_PARAM_ENABLE为true时，可使用${}的方式引用变量。
3. 可以在元命令中使用，也可以在SQL语句中使用。

使用特殊变量VAR_NOT_FOUND来定义引用变量未定义的的处理：

1. default：gsql默认行为，输出原有字符串。
2. null：输出空字符串。
3. error：报错，输出原有字符串。

变量前加冒号引用方式举例：

postgres=# \set StudentId 1
postgres=# \echo :StudentId
1
postgres=# \echo :'StudentId'
'1'
postgres=# \echo :"StudentId"
"1"
postgres=# select :'StudentId' as test;
 test
------
 1
(1 row)

${}引用方式举例：

postgres=# \set DYNAMIC_PARAM_ENABLE true
postgres=# \set StudentId 1
postgres=# \echo ${StudentId}
1
postgres=# \echo '${StudentId}'
1
postgres=# \echo "${StudentId}"
"1"
postgres=# select '${StudentId}' as test;
 test
------
 1
(1 row)

综合案例

使用动态变量创建不同的表，如：

postgres=# \set DYNAMIC_PARAM_ENABLE true
postgres=# select to_char(current_timestamp, 'YYYYMMDD') as "DateInfo";
 DateInfo
----------
 20210609
(1 row)

postgres=# \set TableName data_${DateInfo}
postgres=# create table ${TableName} (id int, description text) distribute by hash(id);
CREATE TABLE
postgres=# \d
                             List of relations
 Schema |     Name      | Type  | Owner |             Storage
--------+---------------+-------+-------+----------------------------------
 public | data_20210609 | table | omm   | {orientation=row,compression=no}
 public | student       | table | omm   | {orientation=row,compression=no}
(2 rows)

将文件内容读入变量，并插入到表中，如：

postgres=# \set DYNAMIC_PARAM_ENABLE true
postgres=# \! cat welcome.txt
Welcome to GaussDB!
postgres=# \set Title `cat welcome.txt`
postgres=# insert into ${TableName} values (1, '${Title}');
INSERT 0 1
postgres=# select * from ${TableName};
 id |     description
----+---------------------
  1 | Welcome to GaussDB!
(1 row)

关于动态变量的内容先介绍这么多，动态变量结合逻辑控制、循环体可以实现更灵活的SQL脚本开发，请继续关注GaussDB(DWS) 客户端gsql功能增强系列博文。

想了解GuassDB(DWS)更多信息，欢迎微信搜索“GaussDB DWS”关注微信公众号，和您分享最新最全的PB级数仓黑科技，后台还可获取众多学习资料哦~