Postgres SQL用法摘记
Postgres SQL用法摘记
1. 游标使用
01
-- Function: send_system_mail()
02 www.zzzyk.com
03
-- DROP FUNCTION send_system_mail();
04
05
CREATE OR REPLACE FUNCTION send_system_mail_to_all(mail_max_count integer, sender_param character varying,
06
title_param character varying, content_param character varying, create_time_param bigint)
07
RETURNS integer AS
08
$BODY$
09
DECLARE
10
charac_cur refcursor;
11
mail_cur refcursor;
12
charac_id_c bigint;
13
mail_count integer;
14
mail_id_c integer;
15 www.zzzyk.com
ret integer;
16
begin
17
open charac_cur for select charac_id from account order by charac_id;
18
loop
19
fetch charac_cur into charac_id_c;
20
if not found then
21
exit;
22
end if;
23
--检查每个角色系统邮件数量是否超限
24
select count(id) from mail where (type&(1<<1)>>1) = 1 and charac_id = charac_id_c into mail_count;
25
if mail_count > mail_max_count then
26
open mail_cur for (select id from mail
27
where (type&(1<<1)>>1) = 1 and charac_id = charac_id_c
28
order by create_time desc limit mail_count - mail_max_count +1);
29 www.zzzyk.com
loop
30
fetch mail_cur into mail_id_c;
31
if not found then
32
exit;
33
end if;
34
--删除多余的邮件
35
delete from mail where id = mail_id_c;
36
--RAISE NOTICE 'value % % %',charac_id_c, mail_count, mail_id_c;
37
end loop;
38
close mail_cur;
39
40
end if;
41
INSERT INTO mail(charac_id, type, create_time, sender, title, content)
42
VALUES (charac_id_c, 2, create_time_param, sender_param,
43
title_param, content_param);
44
end loop;
45
close charac_cur;
46
www.zzzyk.com
47
return 1;
48
49
end
50
$BODY$
51
LANGUAGE plpgsql VOLATILE
52
COST 100;
53
ALTER FUNCTION send_system_mail()
54
OWNER TO postgres;
55
COMMENT ON FUNCTION send_system_mail() IS '发送系统邮件';
