postgresql使用创建存储过程

postgresql创建存储过程

create function pr_update_goods_map_code()
  returns integer
language plpgsql
as $$
declare
   recd RECORD;
   kdId varchar(64);
   outint_cnt Integer;
begin
    outint_cnt = 0;
    create temporary table "goods_map" as
    select '01.03.0004' as old_code,'*非金属矿物制品*玻璃杯-玉晶3801' as old_name,'01.01.0001' as new_code,'*非金属矿物制品*玻璃杯-玉晶3801' as new_name union
    select '07.02.0056' as old_code,'*纸制品*2*5彩色纸片（阻燃）' as old_name,'01.01.0031' as new_code,'*纸制品*2*5彩色纸片（阻燃）' as new_name union
    select '07.02.0188' as old_code,'*纸制品*2*5白色纸片（阻燃整齐款）' as old_name,'01.01.0039' as new_code,'*纸制品*2*5白色纸片（阻燃整齐款）' as new_name union
    select '07.02.0207' as old_code,'100g阻燃电子礼花（80cm）' as old_name,'01.01.0041' as new_code,'100g阻燃电子礼花（80cm）' as new_name union
    select '07.02.0213' as old_code,'*纸制品*彩色阻燃电子礼花（80cm）' as old_name,'01.01.0042' as new_code,'*纸制品*彩色阻燃电子礼花（80cm）' as new_name;
    for recd in (select * from goods_map) loop
        outint_cnt = outint_cnt + 1;
        select id into kdId from t_kd_goods_map where kd_goods_code = recd.old_code and kd_goods_name = recd.old_name and status =1;
        if kdId is null then
            raise notice '%', recd.old_code ||';'||recd.old_name;
        else
           update t_kd_goods_map set kd_goods_code = recd.new_code,update_time =now() where id =kdId;
        end if;
    end loop;
    drop table goods_map;
  return outint_cnt;
end;
$$;