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';
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? |