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

From: Viacheslav N Tararin <taras(at)dekasoft(dot)com(dot)ua>
To: pgsql-bugs(at)postgresql(dot)org
Subject: bug in detection number of updated rows on view with rules.
Date: 2003-10-07 13:37:00
Message-ID: 3F82C17C.1050209@dekasoft.com.ua
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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';

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Stephan Szabo 2003-10-07 18:23:44 Re: Odd Foreign Key Bug
Previous Message Tom Lane 2003-10-07 04:01:58 Re: 7.4beta4: make check broken?