HAWQ技术解析（十） —— 过程语言
HAWQ技术解析（十） —— 过程语言
HAWQ支持用户自定义函数（user-defined functions，UDF），还支持给HAWQ内部的函数起别名。编写UDF的语言可以是SQL、C、Java、Perl、Python、R和pgSQL。其中除SQL和C是HAWQ的内建语言，其它语言通常被称为过程语言（PLs），支持过程语言编程是对HAWQ核心的功能性扩展。HAWQ我所使用过的SQL-on-Hadoop解决方案中唯一支持过程化编程的，Hive、SparkSQL、Impala都没有此功能。对于习惯了编写存储过程的DBA来说，这无疑大大提高了HAWQ的易用性，冲这点也得给HAWQ点个赞。这里主要研究HAWQ内建的SQL语言函数和PL/pgSQL函数编程。为了便于说明，执行下面的SQL语句创建一个名为channel的示例表，并生成一些数据。后面定义的函数大都以操作channel表为例。
create table channel (
id int not null,
cname varchar(200) not null,
parent_id int not null);
insert into channel values (13,'首页',-1);
insert into channel values (14,'tv580',-1);
insert into channel values (15,'生活580',-1);
insert into channel values (16,'左上幻灯片',13);
insert into channel values (17,'帮忙',14);
insert into channel values (18,'栏目简介',17);
select * from channel;
analyze channel;一、HAWQ内建SQL语言
缺省时，在HAWQ的所有数据库中都可以使用SQL和C语言编写用户自定义函数。SQL函数中可执行任意条数的SQL语句。在SQL函数体中，每条SQL语句必须以分号（;）分隔。SQL函数可以返回void或返回return语句指定类型的数据。由于HAWQ只有函数而没有存储过程的概念，returns void可用来模拟没有返回值的存储过程。所有非returns void函数的最后一句SQL必须是返回指定类型的select语句，函数返回最后一条查询语句的结果，可以是单行或多行结果集。下面是SQL函数的几个例子。
create function fn_count_channel() returns bigint as $$
select count(*) from channel;
$$ language sql;该函数没有参数，并返回channel表的记录数，函数的调用结果如图1所示。
图1
修改上面定义的函数：
create or replace function fn_count_channel() returns bigint as $$
select count(*) from channel;
select count(*) from channel where parent_id=-1;
$$ language sql;该函数体内执行了两条查询语句。在函数参数和返回值的定义没有变化时，可以使用create or replace重新定义函数体，该语法与Oracle类似。如果函数参数或返回值的定义发生变化，必须先删除再重建函数。函数返回最后一条查询语句的结果，即parent_id=-1的记录数，调用结果如图2所示。
图2
再次修改fn_count_channel()函数：
create or replace function fn_count_channel() returns bigint as $$
select count(*) from channel;
create table t1 (a int);
drop table t1;
select count(*) from channel where parent_id=-1;
$$ language sql;函数体中也能执行DDL语句，调用结果如图2相同。
改变fn_count_channel()函数的返回值类型，必须先删除再重建，不能使用create or replace语法。
db1=# create or replace function fn_count_channel() returns void as $$
db1$# $$ language sql;
ERROR: cannot change return type of existing function
HINT: Use DROP FUNCTION first.
db1=# select fn_count_channel();
fn_count_channel
------------------
3
(1 row)
db1=# drop function fn_count_channel();
DROP FUNCTION
db1=# create or replace function fn_count_channel() returns void as $$
db1$# $$ language sql;
CREATE FUNCTION
db1=# select fn_count_channel();
fn_count_channel
------------------
(1 row)该函数没有返回值，而且函数体内没有任何SQL语句。
二、PL/pgSQL函数
SQL是关系数据库使用的查询语言，其最大的特点是简单易学，但主要问题是每条SQL语句必须由数据库服务器独立执行，而且缺少必要的变量定义、流程控制等编程手段。过程语言解决的就是这个问题。顾名思义，PL/pgSQL以PostgreSQL作为编程语言。它能实现以下功能：
- 建立plpgsql函数。
- 为SQL语言增加控制结构。
- 执行复杂计算。
- 继承所有PostgreSQL的数据类型（包括用户自定义类型）、函数和操作符。
每条SQL语句由数据库服务器独立执行模式下，客户端应用向数据库服务器发送一个查询请求后，必须等待处理完毕，接收处理结果，做相应的计算，然后再向服务器发送后面的查询。通常客户端与数据库服务器不在同一物理主机上，这种频繁地进程间通信增加了网络开销。使用PL/pgSQL函数，可以将一系列查询和计算作为一组保存在数据库服务器中。它结合了过程语言的强大功能与SQL语言的易用性，并且显著降低了客户端/服务器的通行开销。正因如此，UDF的性能比不使用存储函数的情况会有很大提高。
- 消除了客户端与服务器之间的额外往复，只需要一次调用并接收结果即可。
- 客户端不需要中间处理结果，从而避免了它和服务器之间的数据传输或转换。
- 避免多次查询解析。
PL/pgSQL自动在所有HAWQ数据库中安装。
PL/pgSQL函数参数接收任何HAWQ服务器所支持的标量数据类型或数组类型，也可以返回这些数据类型。除此之外，PL/pgSQL还可以接收或返回任何自定义的复合数据类型，也支持返回单行记录（record类型）或多行结果集（setof record或table类型）。返回结果集的函数通过执行RETURN NEXT语句生成一条返回的记录（与PostgreSQL不同，HAWQ函数不支持RETURN QUERY语法）。
PL/pgSQL可以声明输出参数，这种方式可代替用returns语句显式指定返回数据类型的写法。当返回值是单行多列时，用输出参数的方式更方便。
三、给HAWQ内部函数起别名
许多HAWQ的内部函数是用C语言编写的。这些函数是在HAWQ集群初始化时声明的，并静态连接到HAWQ服务器。用户不能自己定义新的内部函数，但可以给已存在的内部函数起别名。下面的例子创建了一个新的函数fn_all_caps，它是HAWQ的内部函数upper的别名。
create function fn_all_caps (text) returns text as 'upper' language internal strict;该函数的调用结果如图3所示。
图3
四、表函数
表函数返回多行结果集，调用方法就像查询一个from子句中的表、视图或子查询。如果表函数返回单列，那么返回的列名就是函数名。下面是一个表函数的例子，该函数返回channel表中给定ID值的数据。
create function fn_getchannel(int) returns setof channel as $$
select * from channel where id = $1;
$$ language sql;可以使用以下语句调用该函数：
select * from fn_getchannel(-1) as t1;
select * from fn_getchannel(13) as t1;调用结果如图4所示。
图4
与PostgreSQL不同，HAWQ的表函数不能用于表连接。在PostgreSQL中以下查询可以正常执行，如图5所示。
create table t1 (a int);
insert into t1 values (1);
select * from t1,fn_getchannel(13);
图5
但是在HAWQ中，同样的查询会报如图6所示的错误。
图6
单独查询表函数是可以的。
create view vw_getchannel as select * from fn_getchannel(13);
select * from vw_getchannel;查询结果如图7所示。
图7
在某些场景下，函数返回的结果依赖于调用它的参数。为了支持这种情况，表函数可以被声明为返回伪类型（pseudotype）的记录。当这种函数用于查询中时，必须由查询本身指定返回的行结构。下面的例子使用动态SQL，返回结果集依赖于作为入参的查询语句。
create or replace function fn_return_pseudotype ( str_sql text)
returns setof record as
$$
declare
v_rec record;
begin
for v_rec in execute str_sql loop
return next v_rec;
end loop;
return;
end;
$$
language plpgsql;调用函数时必须显式指定返回的字段名及其数据类型。
select * from fn_return_pseudotype('select 1') t (id int);
select * from fn_return_pseudotype('select * from channel') t (id int,cname varchar(200),parent_id int);查询结果如图8所示。
图8
https://www.postgresql.org/docs/8.2/static/datatype-pseudo.html显示了PostgreSQL 8.2支持的伪类型。伪类型不能作为表列或变量的数据类型，但可以被用于函数的参数或返回值类型。
五、参数个数可变的函数
HAWQ从PostgreSQL继承了一个非常好的特性，即函数参数的个数可变。原来做Oracle的时候，想实现这个功能是很麻烦的。参数个数可变是通过一个动态数组实现的，因此所有参数都应该具有相同的数据类型。这种函数将最后一个参数标识为VARIADIC，并且参数必须声明为数组类型。下面是一个例子，实现类似原生函数greatest的功能。
create or replace function fn_mgreatest(variadic numeric[]) returns numeric as $$
declare
l_i numeric:=-99999999999999;
l_x numeric;
array1 alias for $1;
begin
for i in array_lower(array1, 1) .. array_upper(array1, 1)
loop
l_x:=array1[i];
if l_x > l_i then
l_i := l_x;
end if;
end loop;
return l_i;
end;
$$ language 'plpgsql';可以使用如下语句执行该函数。
select fn_mgreatest(array[10, -1, 5, 4.4]);
select fn_mgreatest(array[10, -1, 5, 4.4, 100]);执行结果如图9所示。
图9
六、多态类型
PostgreSQL中的anyelement、anyarray、anynonarray和anyenum四种伪类型被称为多态类型。使用这些类型声明的函数叫做多态函数。多态函数的同一参数在每次调用函数时可以有不同数据类型，实际使用的数据类型由调用函数时传入的参数所确定。
多态参数和返回值是相互绑定的，当一个查询调用多态函数时，特定的数据类型在运行时解析。每个声明为anyelement的位置（参数或返回值）允许是任何实际的数据类型，但是在任何一次给定的调用中，anyelement必须具有相同的实际数据类型。同样，每个声明为anyarray的位置允许是任何实际的数组数据类型，但是在任何一次给定的调用中，anyarray也必须具有相同类型。如果某些位置声明为anyarray，而另外一些位置声明为anyelement，那么实际的数组元素类型必须与anyelement的实际数据类型相同。
anynonarray在操作上与anyelement完全相同，它只是在anyelement的基础上增加了一个额外约束，即实际类型不能是数组。anyenum在操作上也与anyelement完全相同，它只是在anyelement的基础上增加了一个额外约束，即实际类型必须是枚举（enum）类型。anynonarray和anyenum并不是独立的多态类型，它们只是在anyelement上增加了约束而已。例如，f(anyelement, anyenum)与f(anyenum, anyenum)是等价的，实际参数都必须是同样的枚举类型。
如果一个函数的返回值被声明为多态类型，那么它的参数中至少应该有一个是多态的，并且参数与返回结果的实际数据类型必须匹配。例如，函数声明为assubscript(anyarray, integer) returns anyelement。此函数的的第一个参数为数组类型，而且返回值必须是实际数组元素的数据类型。再比如一个函数的声明为asf(anyarray) returns anyenum，那么参数只能是枚举类型的数组。
参数个数可变的函数也可以使用多态类型，实现方式是声明函数的最后一个参数为VARIADIC anyarray。
例1：判断两个入参是否相等，每次调用的参数类型可以不同，但两个入参的类型必须相同
create or replace function fn_equal (anyelement,anyelement)
returns boolean as
$$
begin
if $1 = $2 then
return true;
else
return false;
end if;
end;
$$
language 'plpgsql';下列语句调用函数返回情况如图10所示。
select fn_equal(1,1);
select fn_equal(1,'a');
select fn_equal('a','A');
select fn_equal(text 'a',text 'A');
select fn_equal(text 'a',text 'a');
图10
例2：遍历任意类型的数组，数组元素以行的形式返回。
create or replace function fn_unnest(anyarray)
returns setof anyelement
language 'sql' as
$$
select $1[i] from generate_series(array_lower($1,1),array_upper($1,1)) i;
$$;下列语句调用函数返回情况如图11所示。
select fn_unnest(array[1,2,3,4]);
select fn_unnest(array['a','b','c']);
图11
例3；新建fn_mgreatest1函数，使它能返回任意数组类型中的最大元素。
create or replace function fn_mgreatest1(v anyelement, variadic anyarray) returns anyelement as $$
declare
l_i v%type;
l_x v%type;
array1 alias for $2;
begin
l_i := array1[1];
for i in array_lower(array1, 1) .. array_upper(array1, 1) loop
l_x:=array1[i];
if l_x > l_i then
l_i := l_x;
end if;
end loop;
return l_i;
end;
$$ language 'plpgsql';说明：
- 变量不能定义成伪类型，但可以通过参数进行引用，如上面函数中的l_i v%type。
- 动态数组必须是函数的最后一个参数。
- 第一个参数的作用仅是为变量定义数据类型，所以在调用函数时传空即可。
下列语句调用函数返回情况如图12所示。
select fn_mgreatest1(null, array[10, -1, 5, 4.4]);
select fn_mgreatest1(null, array['a', 'b', 'c']);
图12
七、查看UDF定义
psql的元命令\df可以查看UDF的定义，返回函数的参数与返回值的类型。用命令行的-E参数，还能够看到元命令对应的对系统表的查询语句。
[gpadmin@hdp3 ~]$ psql -d db1 -E
psql (8.2.15)
Type "help" for help.
db1=# \df
********* QUERY **********
SELECT n.nspname as "Schema",
p.proname as "Name",
CASE WHEN p.proretset THEN 'SETOF ' ELSE '' END ||
pg_catalog.format_type(p.prorettype, NULL) as "Result data type",
CASE WHEN proallargtypes IS NOT NULL THEN
pg_catalog.array_to_string(ARRAY(
SELECT
CASE
WHEN p.proargmodes[s.i] = 'i' THEN ''
WHEN p.proargmodes[s.i] = 'o' THEN 'OUT '
WHEN p.proargmodes[s.i] = 'b' THEN 'INOUT '
WHEN p.proargmodes[s.i] = 'v' THEN 'VARIADIC '
END ||
CASE
WHEN COALESCE(p.proargnames[s.i], '') = '' THEN ''
ELSE p.proargnames[s.i] || ' '
END ||
pg_catalog.format_type(p.proallargtypes[s.i], NULL)
FROM
pg_catalog.generate_series(1, pg_catalog.array_upper(p.proallargtypes, 1)) AS s(i)
), ', ')
ELSE
pg_catalog.array_to_string(ARRAY(
SELECT
CASE
WHEN COALESCE(p.proargnames[s.i+1], '') = '' THEN ''
ELSE p.proargnames[s.i+1] || ' '
END ||
pg_catalog.format_type(p.proargtypes[s.i], NULL)
FROM
pg_catalog.generate_series(0, pg_catalog.array_upper(p.proargtypes, 1)) AS s(i)
), ', ')
END AS "Argument data types",
CASE
WHEN p.proisagg THEN 'agg'
WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger'
ELSE 'normal'
END AS "Type"
FROM pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE pg_catalog.pg_function_is_visible(p.oid)
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
ORDER BY 1, 2, 4;
**************************
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+----------------------+------------------+---------------------------------+--------
public | fn_all_caps | text | text | normal
public | fn_count_channel | void | | normal
public | fn_equal | boolean | anyelement, anyelement | normal
public | fn_getchannel | SETOF channel | integer | normal
public | fn_mgreatest | numeric | variadic numeric[] | normal
public | fn_mgreatest1 | anyelement | v anyelement, variadic anyarray | normal
public | fn_return_pseudotype | SETOF record | str_sql text | normal
public | fn_unnest | SETOF anyelement | anyarray | normal
(8 rows)可以看到，用户自定义函数包含在pg_proc系统表中，以下语句查看函数体，查询结果如图13所示。
select prosrc from pg_proc where proname='fn_return_pseudotype';
图13
八、删除UDF
使用drop function <function_name>命令删除函数。注意，在该命令需要加上函数定义的参数类型列表，但不须带参数名。
db1=# drop function fn_mgreatest1;
ERROR: syntax error at or near ";"
LINE 1: drop function fn_mgreatest1;
^
db1=# drop function fn_mgreatest1();
ERROR: function fn_mgreatest1() does not exist
db1=# drop function fn_mgreatest1(anyelement, variadic anyarray);
DROP FUNCTION九、UDF实例——递归树形遍历
经常在一个表中有父子关系的两个字段，比如empno与manager，开篇建立的示例表channel也属于这种结构。在Oracle 中可以使用connect by简单解决此类树的遍历问题，PostgreSQL 9也有相似功能的with recursive语法。
with recursive t (id, cname, parent_id, path, depth) as (
select id, cname, parent_id, array[id] as path, 1 as depth
from channel
where parent_id = -1
union all
select c.id, c.cname, c.parent_id, t.path || c.id, t.depth + 1 as depth
from channel c
join t on c.parent_id = t.id
)
select id, cname, parent_id, path, depth from t
order by path;上面的查询在PostgreSQL中的执行结果如图14所示。
图14
但是，HAWQ不支持with recursive语法，同样的查询，会返回如图15所示的错误。
图15
我们可以使用HAWQ的递归函数功能，自己编写UDF来实现树的遍历。
建立函数从某节点向下遍历子节点，递归生成节点信息，函数返回以‘|’作为字段分隔符的字符串：
create or replace function fn_ChildLst(int, int)
returns setof character varying
as
$$
declare
v_rec character varying;
begin
for v_rec in (select case when node = 1 then
q.id||'|'||q.cname||'|'||q.parent_id||'|'||$2
else fn_ChildLst(q.id, $2 + 1)
end
from (select id, cname, parent_id, node
from (select 1 as node
union all
select 2) nodes, channel
where parent_id = $1
order by id, node) q) loop
return next v_rec;
end loop;
return;
end;
$$
language 'plpgsql';建立节点复合数据类型：
create type tp_depth as (rn int, id int, cname varchar(200), parent_id int, depth int);将fn_ChildLst函数的返回值转换为tp_depth类型：
create or replace function fn_ChildLst_split(int, int)
returns setof tp_depth
as
$$
select cast(rownum as int) rn,
cast(a[1] as int) id,
a[2] cname,
cast(a[3] as int) parent_id,
cast(a[4] as int) depth
from (select rownum,string_to_array(fn_ChildLst,'|') a
from (select row_number() over() as rownum,*
from fn_ChildLst($1, $2)
union all
select 0,id||'|'||cname||'|'||parent_id||'|'||($2 -1) from channel where id = $1)
t) t;
$$
language 'sql';建立查询结果复合数据类型：
create type tp_result as
(id int,
name1 varchar(1000),
parent_id int,
depth int,path varchar(200),
pathname varchar(1000));实现类似Oracle SYS_CONNECT_BY_PATH的功能，递归输出某节点id路径：
create or replace function fn_path(a_id integer)
returns character varying as $$
declare
v_result character varying;
v_parent_id int;
begin
select t.parent_id into v_parent_id
from channel as t where t.id = a_id;
if found then
v_result := fn_path(v_parent_id) || '/' || a_id;
else
return '';
end if;
return v_result;
end;
$$ language 'plpgsql';递归输出某节点的name路径：
create or replace function fn_pathname(a_id integer)
returns character varying as $$
declare
v_result character varying;
v_parent_id int;
v_cname varchar(200);
begin
select t.cname,t.parent_id into v_cname,v_parent_id
from channel as t where t.id = a_id;
if found then
v_result := fn_pathname(v_parent_id) || '/' || v_cname;
else
return '';
end if;
return v_result;
end;
$$ language 'plpgsql';建立输出子节点的函数：
create or replace function fn_showChildLst(int)
returns setof tp_result
as
$$
select t1.id,
repeat(' ', t1.depth)||'--'||t1.cname name1,
t1.parent_id,
t1.depth,
fn_path(t1.id) path,
fn_pathname(t1.id) pathname
from fn_ChildLst_split($1,1) t1
order by t1.rn;
$$
language 'sql';使用下面的语句调用函数，结果如图16至图20所示。
select * from fn_showChildLst(-1);
select * from fn_showChildLst(13);
select * from fn_showChildLst(14);
select * from fn_showChildLst(17);
select * from fn_showChildLst(18);
图16
图17
图18
图19
图20
从某节点向上追溯根节点，递归生成节点信息，函数返回以‘|’作为字段分隔符的字符串：
create or replace function fn_ParentLst(int, int)
returns setof character varying
as
$$
declare
v_rec character varying;
begin
for v_rec in (select case when node = 1 then
q.id||'|'||q.cname||'|'||q.parent_id||'|'||$2
else fn_ParentLst(q.parent_id, $2 + 1)
end
from (select id, cname, parent_id, node
from (select 1 as node
union all
select 2) nodes, channel
where id = $1
order by id, node) q) loop
return next v_rec;
end loop;
return;
end;
$$
language 'plpgsql';将fn_ParentLst函数的返回值转换为tp_depth类型：
create or replace function fn_ParentLst_split(int, int)
returns setof tp_depth
as
$$
select cast(rownum as int) rn,
cast(a[1] as int) id,
a[2] cname,
cast(a[3] as int) parent_id,
cast(a[4] as int) depth
from (select rownum,string_to_array(fn_ParentLst,'|') a
from (select row_number() over() as rownum,* from fn_ParentLst($1, $2)) t) t;
$$
language 'sql';建立输出父节点的函数：
create or replace function fn_showParentLst(int)
returns setof tp_result
as
$$
select t1.id,
repeat(' ', t1.depth)||'--'||t1.cname name1,
t1.parent_id,
t1.depth,
fn_path(t1.id) path,
fn_pathname(t1.id) pathname
from fn_ParentLst_split($1,0) t1
order by t1.rn;
$$
language 'sql';使用下面的语句调用函数，结果如图21至图25所示。
select * from fn_showParentLst(-1);
select * from fn_showParentLst(13);
select * from fn_showParentLst(14);
select * from fn_showParentLst(17);
select * from fn_showParentLst(18);
图21
图22
图23
图24
图25
参考：