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

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