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
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