1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
|
/*
* findoidjoins.c
*
* Copyright (c) 2002-2010, PostgreSQL Global Development Group
*
* $PostgreSQL: pgsql/src/tools/findoidjoins/findoidjoins.c,v 1.8 2010/01/02 16:58:16 momjian Exp $
*/
#include "postgres_fe.h"
#include "libpq-fe.h"
#include "pqexpbuffer.h"
int
main(int argc, char **argv)
{
PGconn *conn;
PQExpBufferData sql;
PGresult *res;
PGresult *pkrel_res;
PGresult *fkrel_res;
char *fk_relname;
char *fk_nspname;
char *fk_attname;
char *pk_relname;
char *pk_nspname;
int fk,
pk; /* loop counters */
if (argc != 2)
{
fprintf(stderr, "Usage: %s database\n", argv[0]);
exit(EXIT_FAILURE);
}
initPQExpBuffer(&sql);
appendPQExpBuffer(&sql, "dbname=%s", argv[1]);
conn = PQconnectdb(sql.data);
if (PQstatus(conn) == CONNECTION_BAD)
{
fprintf(stderr, "connection error: %s\n", PQerrorMessage(conn));
exit(EXIT_FAILURE);
}
/* Get a list of relations that have OIDs */
resetPQExpBuffer(&sql);
appendPQExpBuffer(&sql, "%s",
"SET search_path = public;"
"SELECT c.relname, (SELECT nspname FROM "
"pg_catalog.pg_namespace n WHERE n.oid = c.relnamespace) AS nspname "
"FROM pg_catalog.pg_class c "
"WHERE c.relkind = 'r' "
"AND c.relhasoids "
"ORDER BY nspname, c.relname"
);
res = PQexec(conn, sql.data);
if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
{
fprintf(stderr, "sql error: %s\n", PQerrorMessage(conn));
exit(EXIT_FAILURE);
}
pkrel_res = res;
/* Get a list of columns of OID type (or any OID-alias type) */
resetPQExpBuffer(&sql);
appendPQExpBuffer(&sql, "%s",
"SELECT c.relname, "
"(SELECT nspname FROM pg_catalog.pg_namespace n WHERE n.oid = c.relnamespace) AS nspname, "
"a.attname "
"FROM pg_catalog.pg_class c, pg_catalog.pg_attribute a "
"WHERE a.attnum > 0 AND c.relkind = 'r' "
"AND a.attrelid = c.oid "
"AND a.atttypid IN ('pg_catalog.oid'::regtype, "
" 'pg_catalog.regclass'::regtype, "
" 'pg_catalog.regoper'::regtype, "
" 'pg_catalog.regoperator'::regtype, "
" 'pg_catalog.regproc'::regtype, "
" 'pg_catalog.regprocedure'::regtype, "
" 'pg_catalog.regtype'::regtype, "
" 'pg_catalog.regconfig'::regtype, "
" 'pg_catalog.regdictionary'::regtype) "
"ORDER BY nspname, c.relname, a.attnum"
);
res = PQexec(conn, sql.data);
if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
{
fprintf(stderr, "sql error: %s\n", PQerrorMessage(conn));
exit(EXIT_FAILURE);
}
fkrel_res = res;
/*
* For each column and each relation-having-OIDs, look to see if the
* column contains any values matching entries in the relation.
*/
for (fk = 0; fk < PQntuples(fkrel_res); fk++)
{
fk_relname = PQgetvalue(fkrel_res, fk, 0);
fk_nspname = PQgetvalue(fkrel_res, fk, 1);
fk_attname = PQgetvalue(fkrel_res, fk, 2);
for (pk = 0; pk < PQntuples(pkrel_res); pk++)
{
pk_relname = PQgetvalue(pkrel_res, pk, 0);
pk_nspname = PQgetvalue(pkrel_res, pk, 1);
resetPQExpBuffer(&sql);
appendPQExpBuffer(&sql,
"SELECT 1 "
"FROM \"%s\".\"%s\" t1, "
"\"%s\".\"%s\" t2 "
"WHERE t1.\"%s\"::pg_catalog.oid = t2.oid "
"LIMIT 1",
fk_nspname, fk_relname, pk_nspname, pk_relname, fk_attname);
res = PQexec(conn, sql.data);
if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
{
fprintf(stderr, "sql error: %s\n", PQerrorMessage(conn));
exit(EXIT_FAILURE);
}
if (PQntuples(res) != 0)
printf("Join %s.%s.%s => %s.%s.oid\n",
fk_nspname, fk_relname, fk_attname,
pk_nspname, pk_relname);
PQclear(res);
}
}
PQclear(pkrel_res);
PQclear(fkrel_res);
PQfinish(conn);
termPQExpBuffer(&sql);
exit(EXIT_SUCCESS);
}
|
