PostgreSQL9.4.26/Greenplum6.19.4下，“自定义函数不能在Segment上执行”问题的替代解决方案

01、问题背景

在Greenplum6.19.4数据库中建的存储过程，一般是不适合去互相调用的，否则，一旦被调用的存储过程中,存在SQL查询语句，就有可能触发如下所示错误；但是我试下来，可以如下所示的办法去规避。

如下图所示，在我对Greenplum数据库的生产或实战中，去建立一个自定义的函数，其内含一段SQL语句；然后，将该函数放入另一段SQL语句中，并执行，就会报如下所示错误，意即：不能在Segment子节点上，去执行扫描全表的语句（因为每个Segment上只含全表的一部分语句）

↓↓↓↓摘自《Greenplum企业应用实战》，问题如下案例所示：

1/新建一个自定义处理函数testfunc，参数名为可变字符串类型的pararegistno，好像并无意义
2/函数中，执行一句SQL查询语句，得到10行col1值
3/函数中，设计一个循环，拼接这10行col1值，然后返回一个字符串；函数部分结束
4/单独执行select testfunc(col1::varchar) ，OK；
带入到查询SQL中执行select testfunc(col1::varchar) from test1; 就报错。

02、使用两个自定义函数完成达到上述目的

1/新建一个自定义处理函数如上，不再赘述

2/再新建一个函数，设置一个游标变量为select col1::varchar as col1 from test1;

3/然后遍历该游标时，调该自定义函数emp.col1=testfunc(emp.col1);

4/返回该行，作为结果，直到循环结束(这步返回结果，可能需要再调试，我实战中是直接插入另一个表的)

5/调用第二个建立的函数，select * from test_query() ; 得到想要的结果

代码如下：

-- 删除临时函数
DROP FUNCTION IF EXISTS testfunc(varchar);

-- 创建函数
CREATE OR REPLACE FUNCTION testfunc(pararegistno varchar)

RETURNS  varchar AS 
$$
declare
   id  varchar;
   str varchar;
BEGIN

   str:='';
   for id in select coll from tbl test func limit 10 loop
     str := str||id;
   end loop;
   return str;

END
$$ 
LANGUAGE plpgsql VOLATILE;




-- 删除函数
DROP FUNCTION IF EXISTS test_query();

-- 创建函数
CREATE OR REPLACE FUNCTION test_query()

RETURNS  table(coll VARCHAR(1000))  AS $$

declare
   --声明记录

   emp record;

   --声明游标

   emp_cur cursor for select col1::varchar as col1 from test1;

begin

  --打开游标

  open emp_cur;

  --开启循环loop

  loop

  fetch emp_cur into emp;

  exit when not found;

  --执行上文中的自定义函数testfunc
  emp.col1=testfunc(emp.col1);

  --将每行数据返回
  RETURN emp_cur (emp.col1);

  end loop;

  --关闭游标

  close emp_cur;

end ;

$$ LANGUAGE plpgsql ;


-- 调用函数、达到目的
select * from test_query() ;

3、原理解析

之所以，Greenplum中，自定义函数，执行select testfunc(col1::varchar) from test1; 就报错，是因为Greenplum的master节点，处理接收到的请求时，遇到“select...”语句，就会分发给子节点segment去执行，而并不会进一步解析“select...”语句中的自定义函数，那么，segment上，解析该函数后，发现要执行查询全表的SQL语句，就会报错。

segment上，只存储一张表的部分数据，可能是一个分表，那么，这张分表的名字就与原来的整张表的名字就不一样，所以，可能segment就以此来识别遇到的查询SQL语句，是不是扫描整张表。

我的这种再加一层自定义函数的做法，其实，在开头截图的案例中，已经给出了。因为单独select调用存储过程时不报错，这说明：这种情况下，会在主节点master上去解析自定义函数、执行内含的sql语句。那么同理，这个过程中，再去调用另一个自定义函数，会不会也是在master节点上解析呢，经我测试，答案是会。

也就是说，Greenplum中，单独调用自定义函数，只会在master上解析，只有遇到sql语句，才会分发给子节点segment。只要这个分发给子节点的sql语句，不含自定义函数，就OK了。