欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2024-02-10 14:17:52
...

**

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;