Thread: per-statement-level INSTEAD OF triggers
Hi hackers, I'm asking out of curiosity, do anyone know why we don't have per-statement-level INSTEAD OF triggers? I looked into the standard SQL (20xx draft), but I can't find the restriction such that INSTEAD OF triggers must be row-level. Is there any technical difficulties, or other reasons for the current implementation? Best regards, -- Yugo Nagata <nagata@sraoss.co.jp>
On Mon, Aug 8, 2016 at 4:40 AM, Yugo Nagata <nagata@sraoss.co.jp> wrote: > I'm asking out of curiosity, do anyone know why we don't have > per-statement-level INSTEAD OF triggers? I looked into the > standard SQL (20xx draft), but I can't find the restriction > such that INSTEAD OF triggers must be row-level. Is there > any technical difficulties, or other reasons for the current > implementation? I think one problem is that the trigger wouldn't have any way of knowing the specifics of what the user was trying to do. It would just know the type of operation (INSERT, UPDATE, or DELETE). I guess that could be useful to someone, but not all that useful. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
> On Mon, Aug 8, 2016 at 4:40 AM, Yugo Nagata <nagata@sraoss.co.jp> wrote:
>> I'm asking out of curiosity, do anyone know why we don't have
>> per-statement-level INSTEAD OF triggers? I looked into the
>> standard SQL (20xx draft), but I can't find the restriction
>> such that INSTEAD OF triggers must be row-level. Is there
>> any technical difficulties, or other reasons for the current
>> implementation?
> I think one problem is that the trigger wouldn't have any way of
> knowing the specifics of what the user was trying to do. It would
> just know the type of operation (INSERT, UPDATE, or DELETE). I guess
> that could be useful to someone, but not all that useful.
It might be more useful after we get the infrastructure that Kevin's been
working on to allow collecting all the updates into a tuplestore that
could be passed to a statement-level trigger. Right now I tend to agree
that there's little point.
regards, tom lane
> It might be more useful after we get the infrastructure that Kevin's been > working on to allow collecting all the updates into a tuplestore that > could be passed to a statement-level trigger. Right now I tend to agree > that there's little point. Maybe, this can be used to re-implement FOREIGN KEYs. Never-ending bulk DELETEs caused by lack of indexes on foreign key columns are biting novice users quite often in my experience.