PostgreSQL的存储过程

PostgreSQL的存储过程，公司的询报价表的数据转移，由于修改了很多字段的类型以及状态，大概有7个函数，因为这个函数涉及到的比较全，所以拿出来做例子，详细的就不做说明，主要是给大家看一下过程的结构，应用，内置函数等东西, 包括两张主从表关联插入，希望能帮到大家。

--判断是否是日期类型
DROP FUNCTION IF EXISTS  isdate();
create or replace function isdate(p in varchar) RETURNS integer AS $$
declare
result date;
begin
result:= date(p);
raise notice '%', result;
return 1;
exception
when others then return 0;
end;
$$ LANGUAGE plpgsql;


--开始
DROP FUNCTION IF EXISTS  ChangeInquiry();


CREATE OR REPLACE FUNCTION ChangeInquiry() RETURNS INT AS $$


DECLARE
inquiry RECORD;
delivery INT; --货期
expire TIMESTAMP; --过期时间
i1 RECORD; --询价表
i2 RECORD; --询价商品表
corporation_id INT; --企业表id
category_id INT; --询价商品表分类id
quotation_count INT; --询价商品表报价计数
inquiry2id INT; --询价表新表id
inquiry2goods_id INT; --询价商品表新表id
i2_status INT; --询价商品表状态


BEGIN
for i1 in select * from t_inquiry_price loop


for i2 in select * from t_inquiry_price_prod where inquiry_id = i1.id loop

--询价商品表的货期以及过期时间
if(i2.deliverydate is not null and i2.deliverydate <> '' and isdate(i2.deliverydate)>0) then
select (date(deliverydate) - date(time)) into delivery from t_inquiry_price_prod where id= i2.id;
expire :=i2.deliverydate;
else
delivery := 0;
expire :=i2.time;
end if;

--询价商品表的分类id
if(i2.catalogid = 59036) then
category_id := 1001;
elsif(i2.catalogid = 59037) then
category_id := 1002;
elsif(i2.catalogid = 59038) then
category_id := 1007;
elsif(i2.catalogid = 59039) then
category_id := 1006;
elsif(i2.catalogid = 59040) then
category_id := 1005;
elsif(i2.catalogid = 59041) then
category_id := 1004;
elsif(i2.catalogid = 59042) then
category_id := 1003;
elsif(i2.catalogid = 59043) then
category_id := 1008;
else
category_id := NULL;
end if;

--询价商品表的报价计数
select count(*) into quotation_count from t_quote_price where pid = i2.id;
if NOT FOUND then
quotation_count := 0;
end if;

--询价商品表的状态
if(i2.is_reject = 1) then 
i2_status := 2;
elsif(category_id = NULL and (date(expire)-date(now()))>0 and quotation_count = 0) then
i2_status := 1;
elsif(quotation_count = 0 and (date(expire)-date(now()))>0) then
i2_status := 3;
elsif(quotation_count > 0 and (date(expire)-date(now()))>0) then
i2_status := 4;
elsif((date(now())-date(expire))>0) then
i2_status := 7;
else 
i2_status := 0;
end if;

--询价商品表数据转移到新表
insert into t_inquiry_goods_2(xxxxxxxxxxxxxxxxxx)
values (xxxxxxxxxxxxxxxxxxx)
RETURNING id into inquiry2goods_id;


--询价商品表插入新表id
update t_inquiry_price_prod set inquiry2_goods_id = inquiry2goods_id where id = i2.id;
end loop;


--询价表的企业id
select cid into corporation_id from t_member where id= i1.uid;
if NOT FOUND then
corporation_id := 0;
end if;

--询价表数据转移到新表
insert into t_inquiry_2(xxxxxxxxxxx)
values(xxxxxxxxxxx)
RETURNING id into inquiry2id;


--询价表插入新表id
update t_inquiry_price set inquiry2_id = inquiry2id where id = i1.id;

end loop;
return 1;
END;
$$ LANGUAGE plpgsql;


select ChangeInquiry();