Postgres存储过程

老师要求写个postgres的存储过程，第一次写存储过程，网上postgres存储过程的资料好少啊，查来查去都是两篇被转来转去，写了2天快崩溃了，还是没写出来，中午老师让写一个详细设计给他看，他又帮我修改了一下，最后终于完成了。

 

老师说写东西设计要比编码重要，我的思维是乱的，详细设计写不出来再让我写10天也还是写不出来，要学会怎么查。

 

真的意识到了设计分析的重要性。

 

不多说，说问题：

 

要求：value字段为text类型，存储了一些参数，参数中有换行，需要把value的值按换行符拆分出来存入另一张表。

 

分析：

 

0.先从pressData表中取得一行数据，获得value, transctionId,
1.将value字段拆分开，用split_part()
2.用循环控制取出value字段拆分内容，并依次存入数组
3.取出数组内容依次拆入convert表中transactionid,s001...s060
 insert into tablename values(transactionid,v[0],v[1]...v[59])

拆分函数：

select split_part("Value",'/n',3)
from "pressureData"
order by "ID" desc
limit 1;

存储过程：

split1:

CREATE OR REPLACE FUNCTION split(integer) RETURNS boolean AS$BODY$DECLARE v_transactionid integer; v_value varchar; v_cgq varchar[]; v_i int;BEGIN v_i :=1; while v_i < 61 loop select "TransactionID", split_part("Value",'/n',v_i) into v_transactionid, v_value from "pressureData" where "ID"=$1; v_cgq[v_i] :=v_value; v_i :=v_i+1; end loop; INSERT INTO "convert"( "TransactionID", "S001", "S002", "S003", "S004", "S005", "S006", "S007", "S008", "S009", "S010", "S011", "S012", "S013", "S014", "S015", "S016", "S017", "S018", "S019", "S020", "S021", "S022", "S023", "S024", "S025", "S026", "S027", "S028", "S029", "S030", "S031", "S032", "S033", "S034", "S035", "S036", "S037", "S038", "S039", "S040", "S041", "S042", "S043", "S044", "S045", "S046", "S047", "S048", "S049", "S050", "S051", "S052", "S053", "S054", "S055", "S056", "S057", "S058", "S059", "S060") VALUES (v_transactionid, v_cgq[1], v_cgq[2], v_cgq[3], v_cgq[4], v_cgq[5], v_cgq[6], v_cgq[7], v_cgq[8], v_cgq[9], v_cgq[10], v_cgq[11], v_cgq[12], v_cgq[13], v_cgq[14], v_cgq[15], v_cgq[16], v_cgq[17], v_cgq[18], v_cgq[19], v_cgq[20], v_cgq[21], v_cgq[22], v_cgq[23], v_cgq[24], v_cgq[25], v_cgq[26], v_cgq[27], v_cgq[28], v_cgq[29], v_cgq[30], v_cgq[31], v_cgq[32], v_cgq[33], v_cgq[34], v_cgq[35], v_cgq[36], v_cgq[37], v_cgq[38], v_cgq[39], v_cgq[40], v_cgq[41], v_cgq[42], v_cgq[43], v_cgq[44], v_cgq[45], v_cgq[46], v_cgq[47], v_cgq[48], v_cgq[49], v_cgq[50], v_cgq[51], v_cgq[52], v_cgq[53], v_cgq[54], v_cgq[55], v_cgq[56], v_cgq[57], v_cgq[58], v_cgq[59], v_cgq[60]); return true;END;$BODY$ LANGUAGE 'plpgsql' VOLATILE;

split2:

 

CREATE OR REPLACE FUNCTION split2() RETURNS boolean AS$BODY$DECLARE v_i integer; v_count integer;BEGIN v_i :=1; select count("ID") into v_count from "pressureData"; v_count :=v_count; while v_i <= v_count loop perform * from split(v_i); v_i :=v_i+1; end loop; return true;END;$BODY$ LANGUAGE 'plpgsql' VOLATILE;

 

思路是这样的，在split1(int)中可以将一行中的value拆分出来，当时在split1(int)中写了个嵌套循环，没有实现想要的功能，不知道为什么，所以又写了个split2()来做循环调用split1(int),实验了一下可以成功。

 

经过多次修改得到最终版

 将以上两个函数合并为一个函数并且做到从pressureData表将Value字段拆分出来，然后经过16进制转换（需调用另外一个存储过程16进制转10进制），再经过公式计算，将结果存入

sensorPressure表

CREATE OR REPLACE FUNCTION "public"."pressureDataToSensorPressure" ()RETURNS boolean AS$body$DECLARE v_i integer; v_index integer; v_count integer; --游标初始值为9月一号的ID号，取9月一号9点之后的数据,ID为7797,TransactionID为7787 curs CURSOR FOR select "TransactionID", "RepositoryID","Value","OccourTime" FROM "pressureData" where "ID">=7797 order by "ID" ; v_transactionID integer; v_repositoryID integer; v_value text; v_temp varchar; --v_cgq varchar[]; v_cgq numeric[]; v_OccourTime timestamp without time zone;BEGIN v_i :=1; v_index :=1; open curs; select count("ID") into v_count from "pressureData" where "ID">=7797; while v_i <=v_count loop FETCH curs INTO v_transactionID,v_repositoryID,v_value,v_OccourTime; if v_index >60 then v_index :=1; end if; while v_index <= 60 loop select split_part(v_value,'/n',v_index) into v_temp; v_temp :=trim(both '/n/r' from v_temp); v_cgq[v_index] :=hex_to_int(v_temp); v_cgq[v_index] :=v_cgq[v_index]/100*5-20; v_index :=v_index+1; end loop; INSERT INTO "sensorPressure"( "TransactionID","RepositoryID","OccourTime","S001", "S002", "S003", "S004", "S005", "S006", "S007", "S008", "S009", "S010", "S011", "S012", "S013", "S014", "S015", "S016", "S017", "S018", "S019", "S020", "S021", "S022", "S023", "S024", "S025", "S026", "S027", "S028", "S029", "S030", "S031", "S032", "S033", "S034", "S035", "S036", "S037", "S038", "S039", "S040", "S041", "S042", "S043", "S044", "S045", "S046", "S047", "S048", "S049", "S050", "S051", "S052", "S053", "S054", "S055", "S056", "S057", "S058", "S059", "S060") VALUES (v_transactionid,v_repositoryID,v_OccourTime,v_cgq[1], v_cgq[2], v_cgq[3], v_cgq[4], v_cgq[5], v_cgq[6], v_cgq[7], v_cgq[8], v_cgq[9], v_cgq[10], v_cgq[11], v_cgq[12], v_cgq[13], v_cgq[14], v_cgq[15], v_cgq[16], v_cgq[17], v_cgq[18], v_cgq[19], v_cgq[20], v_cgq[21], v_cgq[22], v_cgq[23], v_cgq[24], v_cgq[25], v_cgq[26], v_cgq[27], v_cgq[28], v_cgq[29], v_cgq[30], v_cgq[31], v_cgq[32], v_cgq[33], v_cgq[34], v_cgq[35], v_cgq[36], v_cgq[37], v_cgq[38], v_cgq[39], v_cgq[40], v_cgq[41], v_cgq[42], v_cgq[43], v_cgq[44], v_cgq[45], v_cgq[46], v_cgq[47], v_cgq[48], v_cgq[49], v_cgq[50], v_cgq[51], v_cgq[52], v_cgq[53], v_cgq[54], v_cgq[55], v_cgq[56], v_cgq[57], v_cgq[58], v_cgq[59], v_cgq[60]); v_i :=v_i+1; end loop; return true;END;$body$LANGUAGE 'plpgsql'VOLATILECALLED ON NULL INPUTSECURITY INVOKERCOST 100;

 

16进制转10进制存储过程：

-- Function: hex_to_int(text)-- DROP FUNCTION hex_to_int(text);CREATE OR REPLACE FUNCTION hex_to_int(text) RETURNS integer AS$BODY$DECLAREh alias for $1;exec varchar;curs refcursor;res int;BEGINexec := 'SELECT x''' || h || '''::int';OPEN curs FOR EXECUTE exec;FETCH curs INTO res;CLOSE curs;return res;END;$BODY$ LANGUAGE 'plpgsql' IMMUTABLE STRICT COST 100;ALTER FUNCTION hex_to_int(text) OWNER TO postgres;

 

 

 

调用存储过程select * from pressureDataToSensorPressure();

 

 

 

 

另外加一个查询函数：

1.按每个月份统计数据把每月的数据统计出来

 

SELECT count("ID") ,(to_char(("OccourTime"), 'YYYY')) || (to_char(("OccourTime"), 'MM')) as "year_month" FROM "pressureData"group by "year_month" ;

 

 

 终于把项目做完了，告一段落，总结补充一下。

最终的存储过程：

CREATE OR REPLACE FUNCTION "public"."pressureDataToSensorPressure" ()RETURNS boolean AS$body$DECLARE v_i integer; v_index integer; v_count integer; --游标初始值为9月一号的ID号，取9月一号9点之后的数据,ID为7797,TransactionID为7787 curs CURSOR FOR select "TransactionID", "RepositoryID","Value","OccourTime" FROM "pressureData" where "ID">=7797 order by "ID" ; v_transactionID integer; v_repositoryID integer; v_value text; v_temp varchar; --v_cgq varchar[]; v_cgq numeric[]; v_OccourTime timestamp without time zone;BEGIN v_i :=1; v_index :=1; open curs; select count("ID") into v_count from "pressureData" where "ID">=7797; while v_i <=v_count loop FETCH curs INTO v_transactionID,v_repositoryID,v_value,v_OccourTime; if v_index >60 then v_index :=1; end if; while v_index <= 60 loop select split_part(v_value,'/n',v_index) into v_temp; v_temp :=trim(both '/n/r' from v_temp); v_cgq[v_index] :=hex_to_int(v_temp); v_cgq[v_index] :=v_cgq[v_index]/100*5-20; v_index :=v_index+1; end loop; INSERT INTO "sensorPressure"( "TransactionID","RepositoryID","OccourTime","S001", "S002", "S003", "S004", "S005", "S006", "S007", "S008", "S009", "S010", "S011", "S012", "S013", "S014", "S015", "S016", "S017", "S018", "S019", "S020", "S021", "S022", "S023", "S024", "S025", "S026", "S027", "S028", "S029", "S030", "S031", "S032", "S033", "S034", "S035", "S036", "S037", "S038", "S039", "S040", "S041", "S042", "S043", "S044", "S045", "S046", "S047", "S048", "S049", "S050", "S051", "S052", "S053", "S054", "S055", "S056", "S057", "S058", "S059", "S060") VALUES (v_transactionid,v_repositoryID,v_OccourTime,v_cgq[1], v_cgq[2], v_cgq[3], v_cgq[4], v_cgq[5], v_cgq[6], v_cgq[7], v_cgq[8], v_cgq[9], v_cgq[10], v_cgq[11], v_cgq[12], v_cgq[13], v_cgq[14], v_cgq[15], v_cgq[16], v_cgq[17], v_cgq[18], v_cgq[19], v_cgq[20], v_cgq[21], v_cgq[22], v_cgq[23], v_cgq[24], v_cgq[25], v_cgq[26], v_cgq[27], v_cgq[28], v_cgq[29], v_cgq[30], v_cgq[31], v_cgq[32], v_cgq[33], v_cgq[34], v_cgq[35], v_cgq[36], v_cgq[37], v_cgq[38], v_cgq[39], v_cgq[40], v_cgq[41], v_cgq[42], v_cgq[43], v_cgq[44], v_cgq[45], v_cgq[46], v_cgq[47], v_cgq[48], v_cgq[49], v_cgq[50], v_cgq[51], v_cgq[52], v_cgq[53], v_cgq[54], v_cgq[55], v_cgq[56], v_cgq[57], v_cgq[58], v_cgq[59], v_cgq[60]); v_i :=v_i+1; end loop; return true;END;$body$LANGUAGE 'plpgsql'VOLATILECALLED ON NULL INPUTSECURITY INVOKERCOST 100;