-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
> Does anyone know how something like this could be done
> in PostgreSQL? I know I can search all the tables that
> I know refer to this table and see if my primary key
> exists, but I want a solution that does not require me to
> rewrite my code every time a new foreign key constraint
> is added to the database.
Here is a function that removes all non-referenced rows from
a table. Make a backup before using of course. :)
CREATE OR REPLACE FUNCTION delete_nonrefs(TEXT) RETURNS TEXT AS '
DECLARE
mytable ALIAS FOR $1;
mytext TEXT;
myrec RECORD;
deltext TEXT;
myrows INTEGER;
BEGIN
mytext := ''
SELECT
c2.relname AS tname,
SUBSTRING((SELECT pg_get_constraintdef(r.oid)) FROM \'\'\\\\\\\\)[^(]+.([^)]+)\'\') AS fkcol,
SUBSTRING((SELECT pg_get_constraintdef(r.oid)) FROM \'\'\\\\\\\\(([^)]+)\'\') AS mycol
FROM pg_class c, pg_class c2, pg_constraint r
WHERE c.relname = \' || quote_literal(mytable) || \'
AND r.confrelid = c.oid
AND r.contype = \'\'f\'\'
AND c2.oid = r.conrelid'';
FOR myrec IN EXECUTE mytext LOOP
IF deltext IS NULL THEN deltext := \' \\nWHERE \';
ELSE deltext := deltext || \'\\nAND \';
END IF;
deltext := deltext || \'NOT EXISTS \' ||
\'(SELECT 1 FROM \' || quote_ident(myrec.tname) || \' t WHERE \' ||
quote_ident(mytable) || \'.\' || myrec.fkcol || \' = t.\' || myrec.mycol || \')\';
END LOOP;
IF deltext IS NULL THEN
RETURN \'Table \' || quote_ident(mytable) || \' is not referenced by any other tables\';
END IF;
deltext := \'DELETE FROM \' || quote_ident(mytable) || deltext;
EXECUTE deltext;
GET DIAGNOSTICS myrows := ROW_COUNT;
RETURN \'Rows deleted from table \' || quote_ident(mytable) || \': \' || myrows;
END;
' LANGUAGE plpgsql STABLE STRICT;
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200312141306
-----BEGIN PGP SIGNATURE-----
iD8DBQE/3KeCvJuQZxSWSsgRAnNwAJ4v0bh/ATZtTaPqqid43qZuaFB/0ACdG+GL
m6AtGBa3tNKsoZmy1ir6/KY=
=S39B
-----END PGP SIGNATURE-----
