postgresSQL 函数的例子

--例子1：
--用函数实现将一个表中插入100条顺序数据 顺序：可以是时间或是序列都可以
CREATE OR REPLACE FUNCTION r_add()
RETURNS integer AS
$BODY$
DECLARE
r RECORD;
BEGIN

FOR r IN 1..100 LOOP
INSERT INTO d_table( d_id, d_name) VALUES (r, 'aa');
END LOOP;

return 0;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION r_add() OWNER TO sqluser;


--例子2：
--在插入的列表中 每第10条记录 的字段中 做一个标记
CREATE OR REPLACE FUNCTION r_update()
RETURNS integer AS
$BODY$
DECLARE
_testvalue int := 0;
r RECORD;
sql varchar;
BEGIN
sql = $$ select d_id,d_name from d_table order by d_id $$;
FOR r IN EXECUTE sql LOOP
_testvalue := _testvalue+1;
if(_testvalue%10=0) then
update d_table set d_name = d_name||'标记' where d_id = r.d_id;
else
continue;
end if;
END LOOP;

return _testvalue;
END;
$BODY$
LANGUAGE 'plpgsql' V
OLATILE
COST 100;
ALTER FUNCTION r_update() OWNER TO sqluser;

--例子3：
--将修改过的记录存在一个备份表中 然后删除原来表中标记过的记录
CREATE OR REPLACE FUNCTION r_detele()
RETURNS integer AS
$BODY$
DECLARE
BEGIN
insert into d_table2 select d_id,d_name from d_table where d_name like '%标记%' order by d_id ;
delete from d_table where d_name like '%标记%';
return null;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION r_detele() OWNER TO sqluser;

--例子4：
--把三个过程联立起来
--插入100条记录 循环标记每第10条to 1条记录 要求每次标记不同 并按顺序备份到 备份表中同时 清空原表
CREATE TABLE d_table
(
d_id integer,
d_name text
)
WITH (
OIDS=FALSE
);
ALTER TABLE d_table OWNER TO sqluser;

CREATE TABLE d_table2
(
d_id integer,
d_name text
)
WITH (
OIDS=FALSE
);
ALTER TABLE d_table2 OWNER TO sqluser;

-- Function: r_update()

-- DROP FUNCTION r_update();

CREATE OR REPLACE FUNCTION r_all()
RETURNS integer AS
$BODY$
DECLARE
i int;
l RECORD;
r RECORD;
n int := 10;
sql varchar;
BEGIN
--插入记录
FOR l IN 1..100 LOOP
INSERT INTO d_table( d_id, d_name) VALUES (l, 'aa');
END LOOP;
--按照规则标记
sql = $$ select d_id,d_name from d_table order by d_id $$;
--先循环最大整除数
while n > 0 LOOP
--每次循环需要重新获取结果集
FOR r IN EXECUTE sql LOOP
i := r.d_id;
if(i%n=0) then
--对一条记录的操作应该在同一个循环中完成
update d_table set d_name = d_name||'被'||n||'整除的标记' where d_id = r.d_id;
insert into d_table2 select d_id,d_name from d_table where d_id = r.d_id;
delete from d_table where d_id = r.d_id;
else
continue;
end if;
end loop;
--循环10次
n := n-1;
END LOOP;
return null;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION r_all() OWNER TO sqluser;