GaussDB(DWS)迁移 - teredata兼容 -- macro兼容 # 【玩转PB级数仓GaussDB(DWS)】

Teradata的宏是一组可以接受参数的SQL语句，通过调用宏名称来执行一段SQL语句，执行上类似于DWS的存储过程。在迁移的时候也建议使用DWS的函数替换TeraData的宏。

测试表定义

CREATE SCHEMA test;
CREATE TABLE test.salary(
    employeeno integer,
    netpay integer
)
WITH (orientation=row, compression=no)
DISTRIBUTE BY ROUNDROBIN;

INSERT INTO test.salary VALUES (1, 1);

TereData宏定义

CREATE MACRO get_emp_salary(employeeno integer) AS ( 
    SELECT 
        employeeno, 
        netpay 
    FROM test.salary 
    WHERE employeeno = :employeeno; 
);

DWS迁移的函数定义

CREATE TYPE test.type_salary AS (employeeno integer, netpay integer);

CREATE OR REPLACE FUNCTION public.get_emp_salary(employeeno integer, OUT employeeno integer, OUT netpay integer)
 RETURNS SETOF test.type_salary
 LANGUAGE sql
 STABLE NOT FENCED SHIPPABLE
AS $function$
    SELECT
        employeeno,
        netpay
    FROM test.salary
    WHERE employeeno = $1;
$function$
;

Note：函数定义中属性要如上定义，需要明确的是

    1)  必须是STABLE

    2) 函数语言必须是SQL语言

    3) 查询语句的返回值类型和函数的出参类型一致

    4)  必须是NOT SHIPPABLE(默认就是NOT SHIPPABLE属性)

    5) 不能定义为STRICT属性(默认就是非STRICT属性)

    6) 不能定义配置参数

    7) 函数的权限属性为SECURITY INVOKER(默认就是SECURITY INVOKER属性)

这样定义的好处是就是当函数体中是简单的单查询语句时，函数调用可以直接优化为对函数体内的SQL语句的调用，执行上避开FuctionSCan的执行层以及SQL不下推的问题，执行上更加高效(具体见下面的case)

语句执行效果

postgres=# EXPLAIN VERBOSE SELECT * FROM get_emp_salary(1);
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
  id |           operation            | E-rows | E-distinct | E-memory | E-width | E-costs
 ----+--------------------------------+--------+------------+----------+---------+---------
   1 | ->  Streaming (type: GATHER)   |      1 |            |          |       8 | 11.01
   2 |    ->  Seq Scan on test.salary |      1 |            | 1MB      |       8 | 1.01

 Predicate Information (identified by plan id)
 ---------------------------------------------
   2 --Seq Scan on test.salary
         Filter: (salary.employeeno = 1)

  Targetlist Information (identified by plan id)
 ------------------------------------------------
   1 --Streaming (type: GATHER)
         Output: salary.employeeno, salary.netpay
         Node/s: All datanodes
   2 --Seq Scan on test.salary
         Output: salary.employeeno, salary.netpay

   ====== Query Summary =====
 -------------------------------
 System available mem: 3072000KB
 Query Max mem: 3072000KB
 Query estimated mem: 1024KB
 Parser runtime: 0.038 ms
 Planner runtime: 0.305 ms
 Unique SQL Id: 3888877297
(26 rows)

【一起来玩转PB级数仓GaussDB(DWS)，分享你的技术经验与体验心得，赢开发者大礼包！】第19期有奖征文火热进行中！