4.4　数据库函数

基于PostgreSQL数据库实现的Greenplum也内置了很多系统函数，用于处理字符串或数字数据。这些系统函数是SQL语句的重要组成部分，可以大大简化运行逻辑，提升查询效率。

按照函数来源，数据库函数可以分为系统函数和自定义函数。系统函数在任何地方使用都不需要带模式名，而自定义函数则需要在函数前加上模式名，以便于程序定位到对应的函数。按照函数类型，系统函数又可以分为数学函数、三角函数、字符串函数、类型转换函数和系统函数。

4.4.1　数学函数

表4-14是Greenplum中提供的数学函数列表。需要说明的是，其中许多函数存在多种形式，区别只是参数类型不同。除非特别指明，任何特定形式的函数都返回和它的参数相同的数据类型。

4.4.2　三角函数列表

三角函数是PostgreSQL的一个亮点，是PostgreSQL数据用于GIS领域的利器。Greenplum同样继承了这些函数和这一优势，表4-15是三角函数列表。

4.4.3　字符串函数和操作符

Greenplum数据库也继承了PostgreSQL数据库丰富的字符串处理函数和操作符函数，具体用法如表4-16所示。

4.4.4　类型转换相关函数

Greenplum是强类型数据库，不能自行转换数据类型，需要在代码中指定类型转换。最常用的方法是用双冒号强制类型转换，例如('2020'||'-01-01')::date即可将拼接字符串强制转换为日期类型。此外，Greenplum数据库也提供了很多类型的转换函数，具体用法如表4-17所示。

4.4.5　自定义函数

虽然Greenplum已经提供了大量内置函数，但是在ETL开发过程中，我们还是会遇到很多需要自定义函数的场景。这里提到的自定义函数，是指有返回值的函数，区别于后文说的存储过程。Greenplum数据库虽然没有存储过程这种对象类型，但是通过返回值为pg_catalog.void的函数，也可以实现存储过程的效果。

由于具有分布式数据的特点，Greenplum数据库自定义函数（有返回值的）不支持在函数中查询赋值语句。

下面以计算支付日期为例进行说明。看上去这个逻辑是一个CASE WHEN判断语句就可以解决的，实际上，这个逻辑是嵌套在一个大的CASE WHEN语句里面的，并且存在多次复用的情况，为了保持代码简洁，我们创建如下自定义函数。

#根据传入的日期进行判断，如果这个日期小于、等于12日，则支付日期为12日；如果大于12日小于、等于25日，则支付日期为25日；如果大于25日，则支付日期为下个月的12日
CREATE OR REPLACE FUNCTION "public"."get_paydate"("datadate" date)
  RETURNS "pg_catalog"."date" AS $BODY$
/******************************************************************
程 序 名：public.get_paydate(datadate)
程序描述：计算支付日期
创建时间：2020-10-28
创 建 人：wcb
修改记录：
修改日期    修改人   修改原因说明
******************************************************************/
DECLARE 
    v_date date; 
    v_rst_date date;
begin
    v_date = datadate; 
   
    select case when to_char(v_date,'dd') <='12' then (to_char(v_date,'yyyy-mm')
        ||'-12')::date
          when to_char(v_date,'dd') <='25' then (to_char(v_date,'yyyy-mm')||'-25')
              ::date
              else ((to_char(v_date,'yyyy-mm')||'-12')::date + interval '1 mon')
              ::date
        end  into v_rst_date;
    
    return v_rst_date;

end;
$BODY$
    LANGUAGE 'plpgsql' VOLATILE COST 100
;

PostgreSQL没有提供类似add_days的函数，在需要对日期增加一个不确定的天数时，语法比较复杂，读者可能不会写，为此我整理了一个函数，如下所示。

CREATE OR REPLACE FUNCTION "public"."add_days"("datadate" date, "days" int4)
  RETURNS "pg_catalog"."date" AS $BODY$
/*****************************************************************/
程 序 名：public.add_days(datadate,months)
程序描述：计算加days天数
创建时间：2019-12-01
创 建 人：wcb
修改记录：
修改日期    修改人   修改原因说明

******************************************************************/
DECLARE 
    v_date date;
    v_days int;
    v_rst_date date;
begin
    v_date = datadate;
    v_days = days;
    
    v_rst_date := date(datadate + (v_days||' day'):: interval);
    
    return v_rst_date;

end;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE COST 100
;

最后分享一个用Python语言定义的函数，Python自定义函数主要用于处理字段级的数据。

CREATE OR REPLACE FUNCTION public.json_parse(data text) returns setof text
AS $$
   import json
   try:
       mydata=json.loads(data)
   except:
      return ['parse error']
   returndata=[]
   try:
      for people in mydata['a']:
    returndata.append(people['b'])
   except:
      return ['223']
   return returndata
$$ LANGUAGE plpythonu;