PostgreSQL自定义函数处理特殊需求

工作中，在某些特殊需求下，基本的SQL语句已经不能满足业务需求，需要使用函数来处理。

1. 需要新建sequence。由于部分表已经有数据，这时，新建的sequence的起始位置不能从1开始。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

CREATE OR REPLACE FUNCTION create_sequence(sequence_name character varying , table_name character varying , column_name character varying )    --sequence_name 是需要新建的sequence名称    --table_name 是sequence作用的表    --column_name 是统计当前表的数据量的列值    RETURNS bigint AS $BODY$      DECLARE      startNum int8;      createsequence "varchar" := '' ;      selectsequence "varchar" := '' ;        BEGIN          --先获取当面表中已经保存了多少数据          selectsequence := 'select COALESCE(max(' ||column_name|| '), 0) + 1 FROM ' || table_name || '' ;          --将查询出来的结果放入startNum中      execute selectsequence into startNum;          --声明 创建 sequence的语句。          createsequence := 'CREATE SEQUENCE ' ||sequence_name|| '       INCREMENT 1       MINVALUE 1       MAXVALUE 9223372036854775807       START ' || startNum || '       CACHE 1' ;      --执行创建sequence的SQL语句      execute createsequence;                 RETURN startNum;      END ; $BODY$    LANGUAGE plpgsql VOLATILE    COST 100; ALTER FUNCTION create_sequence( character varying , character varying , character varying ) OWNER TO postgres;

2. 需求，需要将这两行数据 每列相加。

初始数据：

结果为<喎�"/kf/ware/vc/" target="_blank" class="keylink">vcD4KPHA+PGltZyBzcmM9"/uploadfile/Collfiles/20141219/2014121909224898.jpg" alt="\">

2.1 先将将string转换为数组。如1,2,3,4,5转换为 [1,2,3,4,5]

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

CREATE OR REPLACE FUNCTION tools_str2array(_originstr text)    RETURNS integer [] AS $BODY$ declare _cindex INTEGER ; declare _arrIndex INTEGER ; DECLARE _arr_str int4[]; DECLARE _tmp_str VARCHAR (10); DECLARE _delimeter VARCHAR (1); BEGIN _arrIndex:=1; _cindex:=1; _delimeter:= ',' ;   --_strres:=_strres||'原始字符串是：'||_originStr;   while _cindex< "length" (_originStr) loop --_strres :=_strres||'【这个是什么？】'||split_part(_originStr, _delimeter, _arrIndex); _tmp_str:=split_part(_originStr, _delimeter, _arrIndex); if "character_length" (_tmp_str)<1 then exit; end if; _arr_str:=_arr_str|| CAST (_tmp_str as int4); _arrIndex:=_arrIndex+1; END loop; return _arr_str; end ; $BODY$    LANGUAGE plpgsql VOLATILE    COST 100; ALTER FUNCTION tools_str2array(text) OWNER TO postgres;

2.2. 新增聚集函数。

?

1 2 3 4 5 6

CREATE AGGREGATE hot_map_test( BASETYPE = TEXT, --输入值 SFUNC = csk_test_start, --最初执行的函数 STYPE = TEXT[], --输出类型 FINALFUNC = count_hot_map --最后执行的函数 );

2.3 新建计算函数 计算结果

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47

CREATE AGGREGATE count_test( BASETYPE = TEXT, SFUNC = csk_test_start, STYPE = TEXT[], FINALFUNC = count_hot_map );   CREATE OR REPLACE FUNCTION csk_test_start(a text[], s text)    RETURNS text[] AS $BODY$ BEGIN RETURN a || s; END ; $BODY$    LANGUAGE plpgsql VOLATILE    COST 100; ALTER FUNCTION csk_test_start(text[], text) OWNER TO postgres;   CREATE OR REPLACE FUNCTION count_hot_map(text[])    RETURNS integer [] AS $BODY$      DECLARE      startNum int8;      spilt_sql "varchar" := '' ;      selectsequence "varchar" := '' ;      temp_array int4[];      result_array int4[];        BEGIN          for index in 1..array_length($1 , 1) loop              spilt_sql = 'select tools_str2Array(' '' ||$1[ index ]|| '' ')' ;              execute spilt_sql into temp_array;                            for array_items_index in 1..array_length(temp_array, 1) loop                  if result_array[array_items_index] is null then                     result_array[array_items_index] = 0;                  end if;                        result_array[array_items_index] = result_array[array_items_index] + temp_array[array_items_index];              end loop;          end loop;      RETURN result_array;      END ; $BODY$    LANGUAGE plpgsql VOLATILE    COST 100; ALTER FUNCTION count_hot_map(text[]) OWNER TO postgres;