We like to use With to insert, update and return some value to user. But some
informations of those related tables are not available on that time, is that
a bug ?
with
Master(Master_ID) as (insert into Master(Customer_ID, Field2) values(1,
'BlaBla') returning Master_ID),
Detail as (insert into Detail(Master_ID, Product_ID, ProductValue) select
Master_ID, 5, 50 from Master)
select Master_ID from Master;
This code works but not as expected because we have a trigger which does not
see that data yet.
Suppose a trigger on Detail which needs to find any info from a table which
was inserted on this With. That info is not available, like ...
create function DetailOfDetail() returns trigger() as -- this trigger
function is before insert on Detail
begin
new.Discount = (select discount from Customer inner join Master
using(Customer_ID) where Master_ID = new.Master_ID)
end;
This trigger will not work because Master record was not inserted yet.
If change it to a DO it would work but we would like that result, so ...
This trigger is obviously an example, our tables have more complex
structures but here we want only to understand the way postgres works or if
it´s not working properly.
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html