greenplum数据库 存储过程和函数开发

GREENPLUM使用技巧(四)-greenplum 存储过程和函数开发
上一篇 / 下一篇 2011-07-12 13:10:05 / 个人分类：GREENPLUM

查看( 54 ) / 评论( 4 ) / 评分( 15 / 0 )
greenplum的底层是POSTGRE SQL，
因此greenplum的函数和存储过程实现的主要方式之一是通过PL/pgSQL语言
（也可以调用C、JAVA等语言实现的程序，不是这篇文章的介绍重点）。其具体语法格式如下
CREATE FUNCTION RETURNS AS $$
DECLARE
;
BEGIN
;
;
;

[exception]

when then

/
END;
$$ LANGUAGE plpgsql [volatile|immutable|stable];

在GREENPLUM当中存储过程和函数的界限不明显，如果有具体的返回值，就是函数。
如果没有返回值（return void)就是存储过程。比如函数的定义如下

CREATE OR REPLACE FUNCTION somefunc() RETURNS integer AS $$
DECLARE
quantity integer := 30;
BEGIN
RAISE NOTICE ’Quantity here is %’, quantity; -- Quantity here is 30
quantity := 50;
--
-- Create a subblock
--
DECLARE
quantity integer := 80;
BEGIN
RAISE NOTICE ’Quantity here is %’, quantity; -- Quantity here is 80
END;
RAISE NOTICE ’Quantity here is %’, quantity; -- Quantity here is 50
RETURN quantity;
END;
$$ LANGUAGE plpgsql;

存储过程如下

CREATE OR REPLACE function view.merge_emp()
returns void
as
$$
BEGIN
truncate view.updated_record;

insert into view.updated_record
select y.* from view.emp_edw x right outer join emp_src y on x.empid=y.empid where x.empid is not null;

update view.emp_edw
set deptno=y.deptno,
sal=y.sal
from view.updated_record y
where view.emp_edw.empid=y.empid;

insert into emp_edw
select y.* from emp_edw x right outer join emp_src y on x.empid=y.empid where x.empid is null;
end;
$$ language 'plpgsql';


greenplum的函数/存储过程的调用，如果放在PSQL中调用，可以用下面方式

select view.merge_emp();

如果嵌套在一个过程里调用，用下面的方式。

CREATE FUNCTION RETURNS AS $$
DECLARE
;
BEGIN

perform. view.merge_emp();

[exception]

when then

/
END;
$$ LANGUAGE plpgsql [volatile|immutable|stable];

除了自定义函数以外，GREENPLUM也提供了相关的内置函数和窗口函数供开发者直接使用。


在使用GREENPLUM进行函数/存储过程开发时需要注意的几个重要问题

1、GREENPLUM不支持触发器
2、GREENPLUM中的函数分成三种类型IMMUTABLE, STABLE, VOLATILE
IMMUTABLE类型函数，对于特定的参数总是返回相同的结果，它不能修改数据库。
STABLE类型函数，表示在一次SQL的所有记录中，对于特定的参数，总是返回相同的结果，
但是如果在不同的SQL，可能返回

的结果不同。current_timestamp这样的函数就属于这类函数，
它在一个事务中不会发生变化。
VOLATILE类型函数，即使在一个单表扫描中，相同的参数也可能返回不同的结果。
比如random(), currval(), timeofday()等等。
对于IMMUTABLE类型函数GREENPLUM完全支持，而为了保证数据的一致性，
对于STABLE, VOLATILE函数则是有条件支持，比如如果含有SQL，
这些函数只能在MASTER上执行，而不能再SEGMENT层面执行。比如
SELECT setval('myseq', 201);
SELECT foo(); ---没有from子句。
用户自定义的函数如果没有特别声明，认为是VOLATILE类型的函数。
用来实现聚合操作的函数必须是IMMUTABLE函数

4、如果函数访问对象，则不能在SQL中使用。比如

CREATE OR REPLACE FUNCTION f_channel_id(p_prod_id numeric) RETURNS varchar AS $$
DECLARE
v_channel_id varchar(100) ;
BEGIN
v_channel_id := array(select distinct channel_id from sales where prod_id=44 order by channel_id) ;
return v_channel_id;
END;
$$ LANGUAGE plpgsql immutable;

单独执行ok

sales_demo=# select f_channel_id(44);
f_channel_id
--------------
{4,2,3}
(1 row)

但是如果这样执行

sales_demo=# select prod_id,f_channel_id(prod_id) from sales;


ERROR: function cannot execute on segment because it accesses relation "public.sales" (functions.c:150) (seg10 slice1 sdw3:50002 pid=24798) (cdbdisp.c:1457)
DETAIL:
SQL statement "SELECT array(select distinct channel_id from sales where prod_id=44 order by channel_id)"
PL/pgSQL function "f_channel_id" line 4 at assignment


3、对于SEQUENCE，GREENPLUM不支持lastval和currval函数，setval如果不是操作分布式数据可以使用。

4、用来实现聚合/操作符/类型的函数必须是IMMUTABLE函数

5、每个含有SQL的函数/存储过程,在运行时都是一个独立的事务，运行成功即自动commit，
失败彻底ROLLBACK。如果存在嵌套过程，最外层的存储过程与嵌套存储过程依然作为一个整体事务看待。
比如 A过程中嵌套了过程B,C。如果B执行成功，C执行失败，那么整个A过程失败，B的变化也会回滚。
另外GP中的DELETE/UPDATE都是EXCLUSIVE的表级锁，
所以尽量减少在一个存储过程中嵌入过多的DELETE/UPDATE，
有条件的话把他们分解成独立的存储过程在外部进行调用
。比如我们在ETL过程中，可能要先进行作业状态处理，更新状态标记位，然后开始大批量数据处理，

完成之后再更新状态标记位。这样的一个流程，在ORACLE中可以很容易放在一个存储过程中处理，
并行处理时，不会遇到锁冲突。而GP当中，如果放到一个过程中实现，处理标记位的操作虽然很短，
但是它是作为整个事务的一个部分，而整个过程产生的事务很大，这样在并发运行时会出现很严重的锁冲

突。
所以这时应该把处理状态的DML独立成一个存储过程，处理业务的一个存储过程，
放在外部程序中进行调用。