Re: Multi-row constraints, how to avoid unnecessary trigger execution?

Sándor,
I'd rather have the application developers use regular DML, which could become quite complex, and just perform my check on the database side, at transaction commit time.

Andreas,
thanks, but I need to avoid duplicate executions on different rows too.

I just came up with this "hack" which seems to be working:

create or replace function my_trigger() returns trigger as $$
begin
create temporary table my_trigger() on commit drop;

-- perform expensive test here and raise error if it fails
if ... then
raise ...;
end if;

return null;
exception when duplicate_table then
-- already ran in the current transaction, skip test
return null;
end;
$$ language 'plpgsql';

create constraint trigger my_trigger after insert or update or delete on my_table
initially deferred for each row execute procedure my_trigger();

Any improvement is welcome.

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com