create extension  IF NOT EXISTS "uuid-ossp" ;

--select uuid_generate_v4();

--select current_timestamp;

--select * from (select *,row_number() over(partition by merchant_id order by create_time desc) as num from merchants.merchant_money_record) as temp_tb1 where num=1;

CREATE OR REPLACE FUNCTION for_loop_through_query(

)

RETURNS VOID AS $$

DECLARE

    rec RECORD;

        merchantid uuid;

      balance NUMERIC;

        record_remark VARCHAR;

        account_entity_id uuid;

        account_id uuid;

        transaction_flow_id INT;

        transaction_flow_detail_id INT;

        transaction_time TIMESTAMP;

BEGIN

    DELETE FROM "AccountBook".accounting_entity;

        DELETE FROM "AccountBook".account;

        DELETE FROM "AccountBook".callback_log;

        DELETE FROM "AccountBook".transaction_flow;

        DELETE FROM "AccountBook".transaction_flow_detail;

    FOR rec IN select * from (select *,row_number() over(partition by merchant_id order by create_time desc) as num from merchants.merchant_money_record) as temp_tb1 where num=1

    LOOP

 account_entity_id := uuid_generate_v4();

 account_id := uuid_generate_v4();

 --transaction_flow_id := uuid_generate_v4();

 merchantid := rec.merchant_id;

 balance := rec.current_amount;

 record_remark := rec.record_remark;

 transaction_time := rec.transaction_time;

 INSERT INTO "AccountBook".accounting_entity ("id","type",system_id) VALUES (account_entity_id,1,merchantid);

 INSERT INTO "AccountBook".account ("id","accounting_entity_id","type","balance","createtime","currency_type","status","system_id") VALUES (account_id,account_entity_id,1,balance,current_timestamp,1,1,merchantid);

 INSERT INTO "AccountBook".transaction_flow ("type","business_num","occurrence_time","createtime","remark","accounting_entity_id") VALUES (7,record_remark,transaction_time,current_timestamp,'初始化余额',account_entity_id);

 transaction_flow_id := currval('"AccountBook".transaction_flow_id_seq');

 INSERT INTO "AccountBook".transaction_flow_detail ("transaction_flow_id","account_id","amount","trading_num","current_amount") VALUES (transaction_flow_id,account_id,balance,record_remark,balance);

 transaction_flow_detail_id := currval('"AccountBook".transaction_flow_detail_id_seq');

  RAISE NOTICE '%||%||%||%||%', merchantid,balance,account_entity_id,transaction_flow_id,transaction_flow_detail_id;

    END LOOP;

END;

$$ LANGUAGE plpgsql;

select for_loop_through_query();