postgresql 函數demo

標籤：blog   io   os   ar   使用   for   sp   div   on   

create or replace function refresh_product_usage() returns void as  $$declarerec record;sub_rec record;init_pro_id integer;parent_product_id integer;now_bom_id integer;total_product_qty float;cinsider_efficiency boolean:=true;begin        TRUNCATE TABLE  product_usage;  for rec in select id,bom_id,product_id,product_qty,product_efficiency from mrp_bom where bom_id is not null loopnow_bom_id:=rec.bom_id;total_product_qty:= rec.product_qty;if cinsider_efficiency thentotal_product_qty = total_product_qty/rec.product_efficiency;end if;loopfor sub_rec in select product_id as parent_product_id from mrp_bom where id =now_bom_id loopparent_product_id:=sub_rec.parent_product_id;end loop;if not exists(select id from mrp_bom where bom_id is not null and product_id = parent_product_id ) then --(no record)-->root bomif exists(select id from product_usage where bom_id = now_bom_id and product_id = rec.product_id) thenupdate product_usage set product_qty = product_qty + total_product_qty where  bom_id = now_bom_id and product_id = rec.product_id;elseinsert into product_usage(bom_id,product_id,product_qty) values(now_bom_id, rec.product_id, total_product_qty);end if;exit;elsefor sub_rec in select bom_id,product_qty,product_efficiency from mrp_bom where bom_id is not null and product_id = parent_product_id limit 1 loopnow_bom_id:=sub_rec.bom_id;total_product_qty = total_product_qty* sub_rec.product_qty;if cinsider_efficiency thentotal_product_qty = total_product_qty/sub_rec.product_efficiency;end if;end loop;end if;end loop;end loop;end;$$ LANGUAGE plpgsql;

　

實際上，本來打算唯寫一個sql代碼塊，也就是只要以下部分：

declarerec record;sub_rec record;init_pro_id integer;parent_product_id integer;now_bom_id integer;total_product_qty float;cinsider_efficiency boolean:=true;begin        TRUNCATE TABLE  product_usage;  for rec in select id,bom_id,product_id,product_qty,product_efficiency from mrp_bom where bom_id is not null loopnow_bom_id:=rec.bom_id;total_product_qty:= rec.product_qty;if cinsider_efficiency thentotal_product_qty = total_product_qty/rec.product_efficiency;end if;loopfor sub_rec in select product_id as parent_product_id from mrp_bom where id =now_bom_id loopparent_product_id:=sub_rec.parent_product_id;end loop;if not exists(select id from mrp_bom where bom_id is not null and product_id = parent_product_id ) then --(no record)-->root bomif exists(select id from product_usage where bom_id = now_bom_id and product_id = rec.product_id) thenupdate product_usage set product_qty = product_qty + total_product_qty where  bom_id = now_bom_id and product_id = rec.product_id;elseinsert into product_usage(bom_id,product_id,product_qty) values(now_bom_id, rec.product_id, total_product_qty);end if;exit;elsefor sub_rec in select bom_id,product_qty,product_efficiency from mrp_bom where bom_id is not null and product_id = parent_product_id limit 1 loopnow_bom_id:=sub_rec.bom_id;total_product_qty = total_product_qty* sub_rec.product_qty;if cinsider_efficiency thentotal_product_qty = total_product_qty/sub_rec.product_efficiency;end if;end loop;end if;end loop;end loop;end;

　

但奇怪的是會報很多莫名其妙的語法錯誤：

貌似無法識別很多諸如 record / open 之類的關鍵字。

鬱悶之下寫了個函數。

 

postgresql 用於sql debug輸出可以用：raise notice ‘your_message;%s‘%your_message_var

然後遊標的概念弱化了，與其用cursor，不如直接用 for rec in select .... loop  ....  end loop;

有點小遺憾沒有找到從結果集裡直接賦值的方法。

動態執行sql語句使用DO/EXECUTE

 

postgresql 函數demo