Re: access to original-statement predicates in an INSTEAD-OF row trigger

Adrian Klaver wrote :
>
> On 11/15/19 10:37 AM, John Lumby wrote:
>
> > Suppose the original statement is
>
> > UPDATE myview VW set VW.counter = 11 where VW.primary_key = xxxx and VW.counter = 10;
>
> > and my trigger constructs this statement
>
> > UPDATE basetable BT set BT.counter = 11 where BT.primary_key = OLD.primary_key;
>
> Not following.
>
> Do you want OLD.primary_key set to 11 or BT.counter set to 12/both/or
>
> some other action?

Sorry I did not make it clear.

I want some way for the trigger to discover and apply any predicates *other* than
primary key equality condition that were applied to the original statement,
which in the example is

VW.counter = 10

(the repeated AND in the original append's example was a typo, corrected above)

so for this example I want the trigger to build a statement reading

UPDATE basetable BT set BT.counter = 11 where BT.primary_key = xxxx and BT.counter = 10;

where xxxx is the value of OLD.primary_key

so that, if some other transaction had updated BT.counter to some other value such as 11
in that tiny window I described in previous append,
the result of the generated statement would be no rows updated and a return TAG of 0 rows.

The significance being that the original application would be able to discover
that its update was not applied based on this return TAG
(actually the trigger returns a null tuple to indicate this).

>
> > Cheers, John
>
> Adrian Klaver
>
> adrian(dot)klaver(at)aklaver(dot)com
>