bug in detection number of updated rows on view with rules. - Mailing list pgsql-bugs

From Viacheslav N Tararin
Subject bug in detection number of updated rows on view with rules.
Date
Msg-id 3F82C17C.1050209@dekasoft.com.ua
Whole thread Raw
Responses Re: bug in detection number of updated rows on view with rules.
List pgsql-bugs
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';

pgsql-bugs by date:

Previous
From: Tom Lane
Date:
Subject: Re: 7.4beta4: make check broken?
Next
From: "Ara Anjargolian"
Date:
Subject: Odd Foreign Key Bug