问题2:select string_to_array('a,d,2ed,we4e,dag',',') as ay;--结果 ay -------------------- {a,d,2ed,we4e,dag} (1 行记录)
问题1:存储过程不能直接解析json,因此最好前台把json解析成字符串,传入存储过程进行批量更新。如解析成如下的字符串: "s-001,jack:s-002,tom:s-005,john"--使用postgresql的函数进行行列转换插入到目标的表之中insert into 目标表 select (string_to_array(str,','))[1], (string_to_array(str,','))[2] from unnest(string_to_array('s-001,jack:s-002,tom:s-005,john',':')) as t(str);
非常感谢,问题已解决,存储过程如下:CREATE OR REPLACE FUNCTION _ccgc_test(id text) RETURNS boolean AS $BODY$DECLARE nums text[] := string_to_array(id,'&'); count integer; current_index integer := 1; con_array text[]; BEGIN count := array_upper(nums,1); WHILE current_index <= count LOOP con_array := string_to_array(nums[current_index],':');
INSERT INTO "MonitorPoint" ("PixelCoord","MonitorID") VALUES( con_array[2], con_array[1]);
current_index := current_index +1; END LOOP; return true; END;$BODY$ LANGUAGE plpgsql VOLATILE COST 100; ALTER FUNCTION _ccgc_test(text) OWNER TO postgres;
ay
--------------------
{a,d,2ed,we4e,dag}
(1 行记录)
"s-001,jack:s-002,tom:s-005,john"--使用postgresql的函数进行行列转换插入到目标的表之中insert into 目标表
select (string_to_array(str,','))[1],
(string_to_array(str,','))[2]
from unnest(string_to_array('s-001,jack:s-002,tom:s-005,john',':')) as t(str);
非常感谢,问题已解决,存储过程如下:CREATE OR REPLACE FUNCTION _ccgc_test(id text)
RETURNS boolean AS
$BODY$DECLARE
nums text[] := string_to_array(id,'&');
count integer;
current_index integer := 1;
con_array text[];
BEGIN
count := array_upper(nums,1);
WHILE current_index <= count LOOP con_array := string_to_array(nums[current_index],':');
INSERT INTO "MonitorPoint" ("PixelCoord","MonitorID")
VALUES( con_array[2], con_array[1]);
current_index := current_index +1;
END LOOP;
return true;
END;$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION _ccgc_test(text) OWNER TO postgres;
select _ccgc_test('num-001:tom&num-002:john');
注意:postgreSQL 数组索引从 1 开始
http://www.postgresql.org/docs/8.3/static/functions-array.html