pg 事务 存储过程_postgreSQL执行存储过程，事务出错(为了方便查看，重发一张新帖)...

该楼层疑似违规已被系统折叠 隐藏此楼查看此楼

CREATE OR REPLACE FUNCTION p_dw_dmi_user_d_change_or_new()

RETURNS void AS$BODY$

declare V_PKG VARCHAR(40);

V_PROCNAME VARCHAR(40);

V_DATE VARCHAR(8);

V_ACCT_MONTH VARCHAR(6);

V_DAY VARCHAR(2);

V_DAY2 VARCHAR(2);

begin

V_PKG := 'DW';

V_PROCNAME:='P_DW_DMI_USER_D_CHANGE_OR_NEW';

V_DATE:=to_char(current_date-1,'YYYYMMDD');

--日志部分

perform P_INSERT_LOG(V_DATE,V_PKG, V_PROCNAME, '12', to_char(date_trunc('second', to_timestamp(timeofday(),'Dy Mon DD HH24:MI:SS.US YYYY')),'YYYY-MM-DD HH24:MI:SS'));

V_ACCT_MONTH := SUBSTR(V_DATE,1,6);

V_DAY:=SUBSTR(V_DATE,7,2);

V_DAY2:=SUBSTR(to_char(current_date-2,'YYYYMMDD'),7,2)

begin;

savepoint tt;

--对于在 V_DAY2 有记录、V_DAY没记录的用户也插入

insert into dw_dmi_user_d_change

select a.*

from (select * from dw_dmi_user_d where month_id=V_ACCT_MONTH and day_id=V_DAY2) a

where not exists

(select * from (select * from dw_dmi_user_d where month_id=V_ACCT_MONTH and day_id=V_DAY) b where b.user_no=a.user_no);

perform P_UPDATE_LOG(V_DATE,V_PKG,V_PROCNAME,'SUCCESS',to_char(date_trunc('second', to_timestamp(timeofday(),'Dy Mon DD HH24:MI:SS.US YYYY')),'YYYY-MM-DD HH24:MI:SS'),'完成');

-- EXECUTE sql;

EXCEPTION

WHEN OTHERS THEN

ROLLBACK to tt;

perform P_UPDATE_LOG(V_DATE,V_PKG,V_PROCNAME,'FAIL',to_char(date_trunc('second', to_timestamp(timeofday(),'Dy Mon DD HH24:MI:SS.US YYYY')),'YYYY-MM-DD HH24:MI:SS'),SQLERRM);

commit;

end;

$BODY$ LANGUAGE plpgsql VOLATILE;

执行的时候出错：

等待好心人解答。。。