Re: How to turn off referential integrity

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

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(at)postgresql(dot)org)
>
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (OpenBSD)
Comment: For info see http://www.gnupg.org

iD8DBQE8seESfexLsowstzcRAlJ2AKDpiY67UmWpkV5JDWptfeKt327ScACfSAj7
ryTFhWUADNtUQKdIumgYlzU=
=7QJg
-----END PGP SIGNATURE-----