postgresql function

CREATE or replace FUNCTION faq_sys.dataBackup() RETURNS integer AS
'
declare
ver numeric;
c_row faq_sys.CONTENT%ROWTYPE;
BEGIN
for c_row in (select c.id_, c.version, c.del_flg from faq_sys.content c inner join (select id_, max(version) as version from faq_sys.content group by id_) tmp_c on c.id_ = tmp_c.id_ and c.version = tmp_c.version) loop
if (c_row.del_flg) then
insert into faq_sys.temp_content(id_,version,content_no,service,title) select * from faq_sys.content where id_= c_row.id_;
delete from faq_sys.content where id_ = c_row.id_;
insert into faq_sys.temp_content_status_history(id_,content_id,status_id,user_id) select * from faq_sys.content_status_history where content_id = c_row.id_;
delete from faq_sys.content_status_history where content_id = c_row.id_;
end if;
end loop;
return 1;
END;
'
LANGUAGE 'plpgsql';
posted @ 2019-10-31 14:47  taobr  阅读(266)  评论(0编辑  收藏  举报