bug in detection number of updated rows on view with rules.
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';
Viacheslav N Tararin <taras@dekasoft.com.ua> writes:
Next rule return 0 as count of changed rows but really at lest one
record updated and one inserted
In what PG version? There's been substantial fooling-about with the
semantics of returned counts for rules in recent releases.
In any case, you've not provided nearly enough information to let
someone else reproduce the behavior. Please see
http://www.postgresql.org/docs/7.3/static/bug-reporting.html
regards, tom lane