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



---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

Viacheslav N Tararin <taras (AT) dekasoft (DOT) com.ua> writes:
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/s...reporting.html

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match