CREATE OR REPLACE FUNCTION update_datareporturl() returns void as
$$
DECLARE
recs RECORD;
p integer;
str text;
begin
FOR recs IN SELECT datareport_id AS id,CAST(datareport_url AS TEXT) AS url FROM tbl_datareport
LOOP
p = strpos(recs.url,'&report=');
IF (p > 0) then
str = substr(recs.url,p);
str = substr(str,9);
p = strpos(str,'&');
if (p > 0) THEN
str = substr(str,0,p);
END IF;
EXECUTE 'UPDATE tbl_datareport SET datareport_url='''||str||''' WHERE datareport_id='||recs.id||'';
end IF;
END LOOP;
return;
end;
$$
LANGUAGE 'plpgsql' VOLATILE;
select * from update_datareporturl();
后面会研究一下存储过程中用正则表达式的使用
</script>