postgresql 触发器函数，触发器，游标的使用范例

**

postgresql 触发器函数，触发器，游标的使用范例

一、 触发器函数：

记录数据数目和修改数据集时间的触发器 ：

create or replace function recordNum1() returns trigger as $record_num1$
declare var text;  //定义变量
begin update dataset_type_summary set data_count=(select count(*) from voc2007); 
update dataset_type_summary set create_time = now(); 
var = testCursor();  // 触发器函数调用游标
return new; 
end; 
$record_num1$ 
language plpgsql;

二、触发器的创建：

create trigger recordNum_trigger after insert or delete on database_voc for each row execute PROCEDURE recordNum1();

三、游标的创建：

create or replace function testCursor() returns void as $$ 
declare declare_cursor cursor for select distinct type from image_object where main_id in (select database_id from voc2007); 
var text; 
begin 
open declare_cursor;
fetch declare_cursor into var; 
update dataset_type_summary SET include_type='{}' where id=1; 
while found loop update dataset_type_summary SET include_type = array_append(include_type,var); 
fetch declare_cursor into var; 
end loop; 
close declare_cursor; 
end; 
$$ language plpgsql;