Hi.
All work as expected but...
Next rule return 0 as count of changed rows but really at lest one
record updated and one inserted
---------------------
create or replace rule U_ENTRY
as on update to ENTRY
do instead (
update DISSECTION
set DESCRIPTION = new.DESCRIPTION,
CURR_AMOUNT = new.CURR_AMOUNT,
AMOUNT = new.AMOUNT,
VERSION = (VERSION + 1)%32000
+ gl.RL_ENTRY_MOTION_UPDATE(new.ID, new.D_ACCOUNT_ID,
new.D_ANALYSIS_ID, new.D_STATUS_ID, 'Y')
+ gl.RL_ENTRY_MOTION_UPDATE(new.ID, new.C_ACCOUNT_ID,
new.C_ANALYSIS_ID, new.C_STATUS_ID, 'N')
where ID = new.ID
and VERSION = new.VERSION
);
---------------------
Function body
---------------------
create function gl.RL_ENTRY_MOTION_UPDATE(integer, integer, integer,
integer, char(1)) returns integer as
'
declare
T_DISSECTION_ID alias for $1;
T_ACCOUNT_ID alias for $2;
T_ANALYSIS_ID alias for $3;
T_STATUS_ID alias for $4;
T_IS_DEBIT alias for $5;
T_ID integer := null;
begin
select into T_ID
ID
from MOTION
where DISSECTION_ID = T_DISSECTION_ID
and IS_DEBIT = T_IS_DEBIT;
if ( FOUND ) then
if ( T_ACCOUNT_ID is null ) then
delete from MOTION where ID = T_ID;
else
update MOTION
set ACCOUNT_ID = T_ACCOUNT_ID,
ANALYSIS_ID = T_ANALYSIS_ID,
STATUS_ID = T_STATUS_ID
where ID = T_ID;
end if;
else
if ( T_ACCOUNT_ID is not null ) then
insert into MOTION(ID, ACCOUNT_ID, ANALYSIS_ID, STATUS_ID,
DISSECTION_ID, IS_DEBIT, VERSION)
values (nextval(''MOTION_SQC''), T_ACCOUNT_ID,
T_ANALYSIS_ID, T_STATUS_ID, T_DISSECTION_ID, T_IS_DEBIT, 0);
end if;
end if;
return 0;
end;
'
language 'plpgsql';