Thread: bug in detection number of updated rows on view with rules.

bug in detection number of updated rows on view with rules.

From
Viacheslav N Tararin
Date:
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';

Re: bug in detection number of updated rows on view with rules.

From
Tom Lane
Date:
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