Sándor,
I'd rather have the application developers use regular DML, which could become quite complex, and just perform my check
onthe 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.
-Tobia
