工作中,在某些特殊需求下,基本的sql语句已经不能满足业务需求,需要使用函数来处理。
1. 需要新建sequence。由于部分表已经有数据,这时,新建的sequence的起始位置不能从1开始。
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. 需求,需要将这两行数据 每列相加。
初始数据:
结果为
2.1 先将将string转换为数组。如1,2,3,4,5转换为 [1,5]
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,_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. 新增聚集函数。
CREATE AGGREGATE hot_map_test( BASETYPE = TEXT,--输入值 SFUNC = csk_test_start,--最初执行的函数 STYPE = TEXT[],--输出类型 FINALFUNC = count_hot_map--最后执行的函数 );
2.3 新建计算函数 计算结果
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;