From: | Fabrízio de Royes Mello <fabrizio(at)timbira(dot)com(dot)br> |
---|---|
To: | PegoraroF10 <marcos(at)f10(dot)com(dot)br> |
Cc: | Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: with and trigger |
Date: | 2019-05-29 12:28:50 |
Message-ID: | CAPfkCSAZPTQVSBNbnZsSQFzi9qGkcHuNA0_UeUrnZJkmDLmq_Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Em qua, 29 de mai de 2019 às 08:52, PegoraroF10 <marcos(at)f10(dot)com(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | PegoraroF10 | 2019-05-29 13:10:23 | Re: with and trigger |
Previous Message | Geoff Winkless | 2019-05-29 12:23:10 | Re: with and trigger |