generate raw table relations and sort by 'tsort' later

table_dependencies.py

#!/usr/bin/python

# use PostgreSQL for example
import pgdb

def get_namespace_oid(db, name):
    cx = db.cursor()
    cx.execute('SELECT oid FROM pg_namespace WHERE nspname=%s', (name,))
    row = cx.fetchone()
    cx.close()
    return row and row[0] or None

def get_table_oid(db, name):
    cx = db.cursor()
    cx.execute('SELECT oid FROM pg_class WHERE relname=%s', (name,))
    row = cx.fetchone()
    cx.close()
    return row and row[0] or None

db = pgdb.connect(host=host, user=db_user, password=db_password, database=name)
cx = db.cursor()
cx.execute("SELECT tablename from pg_tables where schemaname='public'")  # none sys tables
tables = [x[0] for x in cx.fetchall()]

nsoid = get_namespace_oid(db, 'public')
oids = {}
for t in tables:
    oid = get_table_oid(db, t)
    oids[oid] = t

d = {}
for t in tables:
    toid = get_table_oid(db, t)
    cx.execute("select confrelid from pg_constraint where connamespace=%d and conrelid=%d and contype='f';" % (nsoid, toid))
    res = cx.fetchall()
    if res:
        x = [oids[i[0]] for i in res]  # [16940, 16304] -> ['tableA', 'tableZ']
        d[t] = x
    else:
        d[t] = None

cx.close()
db.close()

for k,v in d.items():
    if v is None:
        print "%s %s" % (k,'None')
    else:
        for i in v:
            print "%s %s" % (k, i)

# next how to use?
# open a shell && chmod +x table_relations.py
# ./table_relation.py | tsort | grep -v None    # skip 'None' line
# that's the dependencies of tables.

# If have 5 tables: A, B, C, X, Y.
# B references to A & X
# C references to A & Y
# then this program will output:
# A None
# B A
# B X
# C A
# C Y
#
# after sorted by 'tsort':
# B
# C
# X
# Y
# A

test if the dependency is corrent or not. No errors means OK.

for t in ./table_dependencies.py | tsort | grep -v None | tr '\n' ' ';do echo "delete from $t" | psql -U postgres db_name; done

Refs:
http://en.wikipedia.org/wiki/Topological_sorting
http://en.wikipedia.org/wiki/Tsort_(Unix)