FAQ
I have two functions for just that trick. I'm retyping this from another
screen so there may be a typo or two. The DisableTriggers() function does
more work than it has to - I copied the joins from the EnableTriggers()
function which does need the extra joins.

The key is, just set pg_class.reltriggers to either 0 or the correct
number of triggers.

Josh

DROP FUNCTION DisableTriggers();
CREATE FUNCTION DisableTriggers() RETURNS BOOLEAN AS '
DECLARE
Rows INTEGER;
BEGIN
UPDATE pg_class SET reltriggers = 0
FROM (
(SELECT relname,oid FROM pg_class WHERE relname !~
''^pg_'') AS A
JOIN
(SELECT count(*),tgrelid FROM pg_trigger GROUP BY tgrelid)
AS B
ON (A.oid = B.tgrelid)
) AS C
WHERE pg_class.oid = C.oid;
GET DIAGNOSTICS Rows = ROW_COUNT;
IF Rows > 0 THEN
RETURN TRUE;
ELSE
RAISE NOTICE ''Relation does not exist'';
RETURN FALSE;
END IF;
END;
' LANGUAGE 'plpgsql' WITH (isstrict);

DROP FUNCTION EnableTriggers();
CREATE FUNCTION EnableTriggers() RETURNS BOOLEAN AS '
DECLARE
Rows INTEGER;
BEGIN
UPDATE pg_class SET reltriggers = C.Count
FROM (
(SELECT relname,oid FROM pg_class WHERE relname !~
''^pg_'') AS A
JOIN
(SELECT count(*),tgrelid FROM pg_trigger GROUP BY tgrelid)
AS B
ON (A.oid = B.tgrelid)
) AS C
WHERE pg_class.oid = C.oid;
GET DIAGNOSTICS Rows = ROW_COUNT;
IF Rows > 0 THEN
RETURN TRUE;
ELSE
RAISE NOTICE ''Relation does not exist'';
RETURN FALSE;
END IF;
END;
' LANGUAGE 'plpgsql' WITH (isstrict);

Joshua b. Jore
http://www.greentechnologist.org
On Mon, 8 Apr 2002, Pam Wampler wrote:

I am using Postgresql 7.2 -- I would like to know how to turn off the
referential integrity of tables so that I can
do a bulk load & then turn the referential integrity back on.

Thanks very much

Pam Wampler

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Search Discussions

Discussion Posts

Previous

Follow ups

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 2 of 3 | next ›
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedApr 8, '02 at 5:17p
activeApr 8, '02 at 7:20p
posts3
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase