Re: Delete all records NOT referenced by Foreign Keys

-----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-----