GP程序开发规范

GP程序开发规范一．GP程序语法规范1.程序格式GP和oracle不同，没有存储过程的概念，统一为function，无返回值的function就相当于oracle中的procedure。在gp里，函数体被认为是一个字符串文本，所以你需要使用单引号或者美元符界定它，在函数的最后，需要一个LANGUAGE子句，因为gp的函数可以用多种语言来写，一般我们使用plpgsql。没有返回值：CREATEORREPLACEFUNCTIONsp_test()RETURNSvoidAS$BODY$Begin……END$BODY$LANGUAGEplpgsqlVOLATILE;有返回值：CREATEORREPLACEFUNCTIONsp_test()RETURNSnumericAS$BODY$Begin……RETURNres;END$BODY$LANGUAGEplpgsqlVOLATILE;带参数：CREATEORREPLACEFUNCTIONsp_test(v_paravarchar)RETURNSvoidAS$BODY$Begin……END$BODY$LANGUAGEplpgsqlVOLATILE;2.数据类型名字描述charactervarying(n),varchar(n)变长，有长度限制character(n),char(n)定长，不足补空白text变长，无长度限制在gp中，varchar和charactervarying，char和character是等价的，text则相当于oracle中的long，blob，clob等，用于存放超长字符串或二进制文件。名字存储空间描述最低值最高值timestamp[(p)][withouttimezone]8字节日期和时间4713BC5874897ADtimestamp[(p)]withtimezone8字节日期和时间，带时区4713BC5874897ADinterval[(p)]12字节时间间隔-178000000年178000000年date4字节只用于日期4713BC5874897ADtime[(p)][withouttimezone]8字节只用于一日内时间00:00:0024:00:00time[(p)]withtimezone12字节只用于一日内时间，带时区00:00:00+145924:00:00-1459因为interval的存在，所以在oracle中计算时间间隔的方法就无法适用于gp，timestamp类型的数据相减后会生成格式为1day02:20:40.32323这样的数据，需要用extract函数把这个时间以日，时，分来分别计算之后累计，得到具体的间隔。名字存储空间描述范围smallint2字节小范围整数-32768到+32767integer4字节常用的整数-2147483648到+2147483647bigint8字节大范围的整数-9223372036854775808到9223372036854775807decimal变长用户声明精度，精确无限制numeric变长用户声明精度，精确无限制real4字节变精度，不精确6位十进制数字精度doubleprecision8字节变精度，不精确15位十进制数字精度serial4字节自增整数1到2147483647bigserial8字节大范围的自增整数1到92233720368547758073.游标的使用在gp中，游标的定义、使用与oracle略有不同，下面列出一个配合found使用的游标实例。cur_testCURSORforSELECTf_testfromt_test;opencur_test;loopfetchcur_testintov_test;ifFOUNDthen……elseexit;(此处不同于oracle的exitwhencur_test%NOTFOUND)endif;endloop;closecur_conf;4.异常处理抛出异常的方式与oracle不同。Oracle：raise_application_error(-20001,'XXX');Gp：RAISEEXCEPTION'xxx';如果要抛出的异常带有参数，则为：RAISEEXCEPTION'%',parameter;gp同时支持sqlcode和sqlstate两种异常代码，因为sqlcode的时效性已经过了，所以一般采用sqlstate。5.事务在gp中，已经不再支持setautocommit=false（即关闭自动提交）这个特性，所以在函数开头都隐含了一个starttransaction，函数中每一条语句后都带有一个commit，因此我们可以认为gp是没有事务的概念的，即不支持在程序中间进行单独的commit。但仍然支持使用savepoint以及rollbacktosavepoint。在发生异常后，因为gp把整个程序看成一个事务，所以整个程序都会回滚。6.系统函数的使用这里只列出相对关键的系统函数。类型转换函数：函数返回类型描述例子to_char(timestamp,text)text把时间戳转换成字符串to_char(current_timestamp,'HH12:MI:SS')to_char(int,text)text把整数转换成字符串to_char(125,'999')to_date(text,text)date把字符串转换成日期to_date('05Dec2000','DDMonYYYY')to_number(text,text)numeric把字符串转换成数字to_number('12,454.8-','99G999D9S')to_timestamp(text,text)timestampwithtimezone把字符串转换成时间戳to_timestamp('05Dec2000','DDMonYYYY')时间日期函数：函数返回类型描述例子结果clock_timestamp()timestampwithtimezone实时时钟的当前时间戳；current_datedate当前的日期；current_timetimewithtimezone当日时间；current_timestamptimestampwithtimezone当前事务开始时的时间戳；date_part(text,timestamp)doubleprecision获取子域(等效于extract)；date_part('hour',timestamp'2001-02-1620:38:40')20函数返回类型描述例子结果date_trunc(text,timestamp)timestamp截断成指定的精度；date_trunc('hour',timestamp'2001-02-1620:38:40')2001-02-1620:00:00extract(fieldfromtimestamp)doubleprecision获取子域；extract(hourfromtimestamp'2001-02-1620:38:40')20now()timestampwithtimezone当前事务开始时的时间戳；timeofday()text与clock_timestamp相同，但结果是一个text字符串；在事务中，在任何时间使用now，都是相同的值，所以如果要计算某条语句执行的时间，就不可以在执行前后使用now，然后相减来获取执行时间，而应该使用clock_timestamp。7.字典表的使用1.gp中，所有数据库的对象都是用oid连接在一起的。这样子会造成我们在理解数据字典的时候有一些不知所云。下面介绍几个函数，可以简化很多的操作。名字引用描述regprocpg_proc函数名字regprocedurepg_proc带参数类型的函数regoperpg_operator操作符名regoperatorpg_operator带参数类型的操作符regclasspg_class关系名2.获取表的字段信息。表名是放在pg_class，schema名是放在pg_namespace里面的，字段信息是放在pg_attribute里面的。一般是关联这三张表：SELECTa.attname,pg_catalog.format_type(a.atttypid,a.atttypmod)ASdata_typeFROMpg_catalog.pg_attributea,(SELECTc.oidFROMpg_catalog.pg_classcLEFTJOINpg_catalog.pg_namespacenONn.oid=c.relnamespaceWHEREc.relname='table'ANDn.nspname='public')bWHEREa.attrelid=b.oidANDa.attnum0ANDNOTa.attisdroppedORDERBYa.attnum;如果使用regclass就会简化很多：SELECTa.attname,pg_catalog.format_type(a.atttypid,a.atttypmod)ASdata_typeFROMpg_catalog.pg_attributeaWHEREa.attrelid='public.table'::regclassANDa.attnum0ANDNOTa.attisdroppedORDERBYa.attnum;3.获取表的分布键：gp_distribution_policy记录表分布的数据，localoid跟pg_class的oid关联。attrnums是一个数组，记录字段的attnum，跟pg_attribute里面的attnum关联的。selecta.attrnums[i.i],b.attname,a.localoid::regclassfromgp_distribution_policya,(selectgenerate_series(1,10))i(i),pg_attributebwherea.attrnums[i.i]isnotnullanda.localoid=b.attrelidanda.attrnums[i.i]=b.attnumanda.localoid='public.table'::regclassorderbyi.i;除上述之外，还可以用数据字典查询一个表是否分区表、查询一个表的分区键、查询分区表每个分区的具体信息、查询备注信息、查询权限信息、查询表的依赖关系等。8.二．GP程序注释规范1.程序开头注释/***函数名称：列出函数的名称*函数描述：大概描述函数要实现的功能，其中有哪些关键的处理，并描述清楚实现的逻辑*操作说明：具体说明函数的每个步骤操作，以及操作的逻辑*输入参数：具体说明每个输入参数的功能，如有特殊参数，需做详细的参数值描述*输出参数：具体说明每个输出参数的作用*编写人员：函数编写人，如有修改，应标注，如：modifybygodot*创建日期：函数创建的日期，如有修改，应标注，如：modifybygodot_2012-2-1*公司名称：函数编写人所在公司**/2.程序分段注释---------step1：description应说明具体是哪个步骤，如有顺序，则应排列如step1，step2…并在后面具体将该分步骤的功能描述清楚，如有输出结果，则应描述期待结果。如：---------第一步:把关联数据插入历史表,判断增量表的end_time3.程序内特殊处理注释针对程序代码中，某些特殊的应用，需要用注释来标注，包括引用其他函数、引用特殊的函数、判断的开头、循环的开头、新增、修改等等。如：---------此处引用了另一个函数getxx，用来获取表的全部字段三．日志记录规范在程序中，应该记录相应的日志，包括每一个阶段要执行的sql语句，执行的结果，执行语句耗时等。以下是一个标准的日志记录流程：t_begin_time:=clock_timestamp();EXECUTEv_dynamic_sql;GETDIAG