PostgreSQL function Demo

create or replace function refresh_product_usage() returns void as  $$
declare
	rec 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 loop
		now_bom_id:=rec.bom_id;
		total_product_qty:= rec.product_qty;
		if cinsider_efficiency then
			total_product_qty = total_product_qty/rec.product_efficiency;
		end if;
		loop	
			for sub_rec in select product_id as parent_product_id from mrp_bom where id =now_bom_id loop
				parent_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 bom
				if exists(select id from product_usage where bom_id = now_bom_id and product_id = rec.product_id) then
					update product_usage set product_qty = product_qty + total_product_qty where  bom_id = now_bom_id and product_id = rec.product_id;
				else
					insert into product_usage(bom_id,product_id,product_qty) values(now_bom_id, rec.product_id, total_product_qty);
				end if;
				exit;
			else
				for 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 loop
					now_bom_id:=sub_rec.bom_id;
					total_product_qty = total_product_qty* sub_rec.product_qty;
					if cinsider_efficiency then
						total_product_qty = total_product_qty/sub_rec.product_efficiency;
					end if;
				end loop;
			end if;
								
		end loop;
		
	end loop;
end;
$$ LANGUAGE plpgsql;


	

In fact, it was intended to write only a block of SQL code, that is, just the following sections:

declare
	rec 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 loop
		now_bom_id:=rec.bom_id;
		total_product_qty:= rec.product_qty;
		if cinsider_efficiency then
			total_product_qty = total_product_qty/rec.product_efficiency;
		end if;
		loop	
			for sub_rec in select product_id as parent_product_id from mrp_bom where id =now_bom_id loop
				parent_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 bom
				if exists(select id from product_usage where bom_id = now_bom_id and product_id = rec.product_id) then
					update product_usage set product_qty = product_qty + total_product_qty where  bom_id = now_bom_id and product_id = rec.product_id;
				else
					insert into product_usage(bom_id,product_id,product_qty) values(now_bom_id, rec.product_id, total_product_qty);
				end if;
				exit;
			else
				for 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 loop
					now_bom_id:=sub_rec.bom_id;
					total_product_qty = total_product_qty* sub_rec.product_qty;
					if cinsider_efficiency then
						total_product_qty = total_product_qty/sub_rec.product_efficiency;
					end if;
				end loop;
			end if;
								
		end loop;
		
	end loop;
end;

But it is strange to report a lot of inexplicable grammatical errors:

I don't seem to recognize a lot of keywords like record/open.

Depressed under wrote a function.

PostgreSQL for SQL debug output can be used: raise notice ' your_message;%s '%your_message_var

Then the concept of the cursor is weakened, instead of using the cursor, use the For rec in select ... loop ... end loop directly;

It's a little bit unfortunate. The method of assigning values directly from the result set is not found.

Dynamic execution of SQL statements using Do/execute

PostgreSQL function Demo