Em qua, 29 de mai de 2019 às 08:52, PegoraroF10 <
marcos@f10.com.br> escreveu:
>
> 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.
>
To see updated data using a trigger in the same transaction you should create trigger on AFTER event.
Regards,
--
Fabrízio de Royes Mello Timbira -
http://www.timbira.com.br/ PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
