# 使用数据库方言sql_dialect插件实现通用语法和功能转换
sql_dialect为DWS数据库SQL方言插件，主要用于在不同数据库系统（如MySQL、PostgreSQL、Oracle、SQL Server等）之间实现通用语法和功能转换。
#### 注意事项
- 方言插件中函数优先级高于数据库内核中的函数，便于解决与内核的冲突。
- 每一个数据库可以独立绑定方言插件，与执行CREATE DATABASE时选择的DBCOMPATIBILITY参数没有关联关系，但建议保持一致。
- 方言插件一旦绑定，不支持修改，除非先卸载插件再重新绑定。
 
#### sql_dialect插件基础能力
- 支持简单SQL实现的函数。
- 支持PL/pgSQL实现的函数。
- 支持C实现的函数。
- 支持独立的升降级，与数据库内核升降级无关。
 
#### sql_dialect插件包结构
```
├── lib
│   └── postgresql
│       └── libsql_dialect.so --插件动态库文件
└── share
    └── postgresql
        └── extension
            ├── sql_dialect.control --插件控制文件
            ├── sql_dialect_mysql.sql --mysql方言支持的对象文件
            ├── sql_dialect--1.0.0.sql
            ├── sql_dialect--1.0.0--1.0.1.sql
            └── sql_dialect--1.0.1--1.0.0.sql
```
#### sql_dialect插件使用
sql_dialect插件支持绑定，卸载及升降级操作。
#### 绑定方言插件
1. sql_dialect插件已经正确安装部署。
2. 自动执行插件中的sql_dialect_mysql.sql文件，并确认在__mysql__的schema下有插件自带函数。当前会话可直接使用这些插件函数。
3. 执行以下命令绑定方言插件。 
   ```
   ALTER DATABASE database_name SET sql_dialect='mysql';
   ```
   
   
4. 发送信号，通知其它在线会话优先使用__mysql__的schema下的函数，优先级高于pg_catalog。 
   ```
   SELECT pg_reload_conf();
   ```
   
   
5. 使用PG_EXTENSION系统表查询自动安装的sql_dialect插件。 
   ```
   SELECT * FROM pg_extension WHERE extname='sql_dialect';
         extname      | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition 
   -------------------+----------+--------------+----------------+------------+-----------+--------------
    sql_dialect       |       10 |           11 | f              | 1.0.0      |           | 
   (1 row)
   ```
   
   
6. 使用PG_NAMESPACE系统表查询自动创建的schema。 
   ```
   SELECT * FROM pg_namespace WHERE nspname='__dialect_mysql__';
         nspname      | nspowner | nsptimeline | nspacl | permspace | usedspace | nsptype 
   -------------------+----------+-------------+--------+-----------+-----------+---------
    __dialect_mysql__ |       10 |           0 |        |        -1 |         0 | i
   (1 row)
   ```
   
   
7. 使用PG_PROC系统表查询方言插件自带的函数，方言插件自带函数的选取优先级会高于数据库内核函数，且用户使用时无需指定schema。 
   ```
   SELECT proname,nspname,prosrc FROM pg_proc,pg_namespace n WHERE pronamespace=n.oid and nspname='__dialect_mysql__';
       proname    |      nspname      |                   prosrc                    
   ---------------+-------------------+---------------------------------------------
    rlike         | __dialect_mysql__ | select $1 ~ $2
   ```
   
   
 
#### 卸载方言插件
使用DROP EXTENSION卸载当前数据库的方言插件。
```
DROP EXTENSION sql_dialect;
```
#### 升降级方言插件
1. 确认当前数据库支持的扩展版本。 
   ```
   SELECT * FROM pg_extension;
   ```
   
   
2. 升级扩展到最新版本。 
   ```
   ALTER EXTENSION extension_name UPDATE;
   ```
   
   
3. 升级扩展到指定版本。 
   ```
   ALTER EXTENSION extension_name UPDATE TO 'x.x.x';
   ```
   
   
 
#### sql_dialect插件中的函数开发
通过了解函数属性、优化函数提升插件函数性能和兼容性。
#### 函数属性
可以参考[CREATE FUNCTION](https://support.huaweicloud.com/sqlreference-dws/dws_06_0163.html)章节了解函数的属性及函数是否下推。
#### inline优化提高性能
inline优化，是数据库查询优化器的一项功能，类似C++中的inline能力，如果是简短的计算或转换函数，且符合inline优化条件时，优化器会将函数调用优化为表达式执行，避免函数调用的额外开销，显著提升执行性能。
inline优化示例：
```
CREATE FUNCTION func_add_sql(integer, integer) RETURNS integer
AS 'select $1 + $2;'
LANGUAGE SQL IMMUTABLE;
```
该函数执行加法操作，符合inline优化条件，优化器可将其替换为表达式"$1 + $2"。
执行计划显示Output字段为优化后的表达式 (a + b)，而非函数调用。
```
EXPLAIN VERBOSE SELECT func_add_sql(a, b) FROM t1;
                                        QUERY PLAN                                        
------------------------------------------------------------------------------------------
  id |          operation           | E-rows | E-distinct | E-memory | E-width | E-costs 
 ----+------------------------------+--------+------------+----------+---------+---------
   1 | ->  Streaming (type: GATHER) |      1 |            |          |       8 | 9.01    
   2 |    ->  Seq Scan on public.t1 |      1 |            | 1MB      |       8 | 1.01    
      Targetlist Information (identified by plan id)     
 --------------------------------------------------------
   1 --Streaming (type: GATHER)
         Output: ((a + b))
         Node/s: All datanodes (node_group, bucket:16384)
   2 --Seq Scan on public.t1
         Output: (a + b)
```
**inline函数要求**：
- 函数类型，LANGUAGE必须是SQL类型。
- 函数易变性，不能是VOLATILE，且易变性不高于函数体内的语句。例如：函数体内调用的函数其易变性为STABLE，则该函数只能是STABLE，不能是IMMUTABLE。从高到低按照IMMUTABLE STABLE VOLATILE排序。
- 函数strict属性，必须与函数体内包含的函数strict一致。
- 函数体，函数中必须为简单的单条select语句，不含group by等复杂逻辑。
- 函数返回值，函数内语句返回类型必须与该函数返回类型一致，且不能是set和record等复杂类型。
#### 入参含NULL优化
入参含NULL返回值也肯定为NULL的函数，要显式定义STRICT属性。这样当入参含NULL时，可以减少函数体的调用执行。
#### 函数类型选择
为提升性能与稳定性，建议按以下顺序选择函数类型：SQL函数 \> C函数 \> plpgsql。
- SQL函数：简单计算优先使用SQL类型，尽量满足inline特性，避免函数调用开销。
- C函数：非单条语句的函数建议定义为C函数。 严格遵循内存申请释放原则；保护模式必须显式定义为FENCED模式；严格校验入参与返回值类型，避免类型不一致或强制转换导致结果集问题或集群异常。
  

- plpgsql函数：逻辑复杂C函数实现较为困难的场景可以选择plpgsql类型。
#### 函数内设置GUC变量优化
尽量避免在函数内部设置环境变量，造成不必要的通信开销。
```
CREATE OR REPLACE FUNCTION func_increment_plsql(i integer) RETURNS integer
SET autoanalyze=off --这里执行，不会跨CN进行设置
AS $$
BEGIN
SET autoanalyze=off; --这里执行，会跨CN进行设置，有通信开销
RETURN i + 1;
END;
$$ LANGUAGE plpgsql;
```
#### VOLATILE的SQL函数优化
VOLATILE属性的SQL语言函数会频繁访问GTM，导致性能较差，且给GTM造成较大的压力，避免将SQL函数定义为VOLATILE属性。
#### 其它注意事项
- 数据类型 函数参数的类型和返回值类型，要严格校验，避免类型转换引发集群异常或结果集错误。例如以下场景：
  - 应返回timestamptz，而返回了text。结果表面显示一致，但无法参与时区转换或时间计算。
  
  - 应返回timestamptz，而返回了timestamp，丢失时区信息，导致时间计算偏差。
  
  - 不兼容类型的强制转换，导致C函数调用报错。
  
  - 调用内存C函数时，需要传入timestamptz类型，却传入timestamp类型，导致时区转换错误。
   
- 函数重载 对于能自动隐式类型转换的类型（如text、timestamp到timestamptz），优先定义一个统一函数，避免冗余实现。
  
- 函数开发 推荐使用DWS原生函数定义新函数。因为兼容性函数可能因为版本更新等兼容性问题导致新函数结果集不稳定。
  
- 函数变更 函数参数个数和类型，一旦定义，不允许变更，避免用户业务逻辑报错。
  函数行为也禁止前后不兼容的变更，如果必须要变更，需通过数据库GUC参数控制，避免与数据库依赖冲突。
  
 
#### 函数开发示例
新函数需添加至dialects目录对应的方言文件中，同时还要在升级脚本中再进行处理。
- SQL函数
  ```
  CREATE FUNCTION func_add_sql(integer, integer) RETURNS integer
  AS 'select $1 + $2;'
  LANGUAGE SQL IMMUTABLE;
  ```
  
- PLSql函数。PL/pgSQL函数的开发，要遵守PL/pgSQL使用。
  ```
  CREATE OR REPLACE FUNCTION func_increment_plsql(i integer) RETURNS integer
  AS $$
  BEGIN
  RETURN i + 1;
  END;
  $$ LANGUAGE plpgsql;
  ```
  
- C函数 通过独立C函数（少量使用DWS基础函数），都可以添加至插件。在插件中现有或新增的cpp中编写函数：
  ```
  PG_FUNCTION_INFO_V1(rand_seed);
  extern "C" Datum rand_seed(PG_FUNCTION_ARGS);
  Datum rand_seed(PG_FUNCTION_ARGS)
  {
  int128 n = PG_ARGISNULL(0) ? 0 : PG_GETARG_INT64(0);
  int elevel = ERROR;
  if (unlikely(n > PG_UINT64_MAX)) {
  elog(elevel, "Truncated incorrect DECIMAL value");
  n = PG_UINT64_MAX;
  } else if (unlikely(n < PG_INT64_MIN)) {
  elog(elevel, "Truncated incorrect DECIMAL value");
  n = PG_INT64_MIN;
  }
  gs_srandom((unsigned int)n);
  float8 result;
  /* result [0.0 - 1.0) */
  result = (double)gs_random() / ((double)MAX_RANDOM_VALUE + 1);
  PG_RETURN_FLOAT8(result);
  }
  ```
  在插件的sql文件中注册C函数：
  ```
  CREATE OR REPLACE FUNCTION __dialect_mysql__.rand_seed(int)
  returns double precision
  LANGUAGE C
  volatile NOT FENCED
  as
  '$libdir/libsql_dialect', 'rand_seed';
  ```