Re: disable triggers using psql

On Feb 17, 2011, at 6:59 AM, Geoffrey Myers wrote:

>> Unless something very big changed when I wasn't looking, the
>> constraints are actually implemented as triggers under the hood. But
>> you're right that it'd be cleaner to drop the constraints and re-add
>> them than to fool with system triggers.
>
> We were trying to accomplish this without having to hack the dump to much. We attempted adding:
>
> set local session_replication_role = replica;
>
> But that does not seem provide the expected relief.

If your triggers have some simple way of identifying them in a query on pg_trigger, the function below can be altered to easily enable or disable them.

John DeSoi, Ph.D.

=====

create or replace function enable_link_clean_triggers(p_enable boolean)
returns void as $$
declare
v_action text;
v_sql text;
v_tg record;
begin
if p_enable then
v_action = ' ENABLE TRIGGER ';
else
v_action = ' DISABLE TRIGGER ';
end if;
for v_tg in select tgrelid, tgname from pg_trigger where tgname ~ '^tg_link_clean_.+' loop
v_sql = 'ALTER TABLE ' || v_tg.tgrelid::regclass::text || v_action || v_tg.tgname || ';';
execute v_sql;
end loop;
return;
end;
$$ language plpgsql;