I misunderstood you.
What you are saying is that since the insert is done first and then the update when it gets to the
update it checks the rule and even though it wasn't valid when the statement was executed, it is
valid at the time of execution and therefore it runs.
I have tried another test, with the assumption that the new.stockid (serial field) would be the
number of the inserted row, but it's not.
create or replace rule rul_insertstock as on insert
to stock where exists(select stockid from stock where pnid=new.pnid
and ownerid=new.ownerid and coalesce(stocklocationid,-1)=coalesce(new.stocklocationid,-1)
and stockid<>new.stockid)
Do Instead
select func_rul_insertstock(new.stock,new.pnid,new.stocklocationid,new.stockid);
the stockid<>new.stockid should check if the insert statement happened or not. However, the
new.stockid jumps 3 numbers every time an insert statement is called, whether or not the insert
actually happens.
For example, if the sequence is at 100 and the insert statement does not meet the rule, meaning that
it will do an insert, the record is inserted with a stockid of 101 and the new.stockid shows 103 and
the sequence start is at 103.
If the insert statement meets the rule and does not do an insert then the new.stockid and the
sequence start is still at 103.
In the case that the insert statement works, the update statement is still run because the
new.stockid does not reflect the actual new.stockid.
Martijn van Oosterhout wrote:
On Tue, Mar 13, 2007 at 02:15:01PM +0200, Sim Zacks wrote:
select version()
"PostgreSQL 8.0.1 on i686-pc-linux-gnu, compiled by GCC
i686-pc-linux-gnu-gcc (GCC) 3.3.5 (Gentoo Linux 3.3.5-r1,
ssp-3.3.2-3, pie-8.7.7.1)"
I am sure that I must have missed something here because I read the
documentation and searched the forums and it all seems fairly
straightforward.
Rules don't work the way you think they do. They're sort of macro
expansions. What's ahppening when you insert is the rule splits it into
two statements, one insert and one update, with the where conditions
adjusted. Depending on the order I imagine that it could do both.
I don't think rules can do what you want. What you need in the SQL
MERGE command, but postgresql doesn't support that. A stored procedure
could do it.
Have a nice day,
