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
