Check if a given table has any reference to it

check_fk_reference.sql

DO $$
DECLARE
        r RECORD;
        ret BOOLEAN;
        cmd TEXT;
        check_table regclass = '<table name here>'::regclass;
BEGIN
        FOR r IN
                SELECT
                        c.conname,
                        c.conrelid::regclass::text AS referencing,
                        c.confrelid::regclass::text AS referenced,
                        string_agg('a.'||quote_ident(af.attname) || ' = ' || 'b.'||quote_ident(a.attname)||' AND a.'||quote_ident(af.attname) || ' IS NOT NULL AND b.'||quote_ident(a.attname) || ' IS NOT NULL ', ' AND ') AS condition
                FROM pg_constraint c
                LEFT JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum =ANY(c.conkey)
                LEFT JOIN pg_attribute af ON af.attrelid = c.confrelid AND af.attnum =ANY(c.confkey)
                WHERE c.contype = 'f' AND c.confrelid = check_table
                GROUP BY c.conname, c.conrelid, c.confrelid
        LOOP
                cmd := format('SELECT EXISTS(SELECT 1 FROM %s b WHERE EXISTS(SELECT 1 FROM %s a WHERE %s))', r.referencing, r.referenced, r.condition);
                --RAISE NOTICE 'SQL: %', cmd;
                EXECUTE cmd INTO ret;
                IF (ret) THEN
                        RAISE '% is still referenced by % (FK: %)', r.referenced, r.referencing, r.conname;
                END IF;
        END LOOP;
END;
$$;