4 * Copyright (c) 2002-2015, PostgreSQL Global Development Group
6 * src/tools/findoidjoins/findoidjoins.c
8 #include "postgres_fe.h"
11 #include "pqexpbuffer.h"
15 main(int argc, char **argv)
28 pk; /* loop counters */
32 fprintf(stderr, "Usage: %s database\n", argv[0]);
36 initPQExpBuffer(&sql);
38 appendPQExpBuffer(&sql, "dbname=%s", argv[1]);
40 conn = PQconnectdb(sql.data);
41 if (PQstatus(conn) == CONNECTION_BAD)
43 fprintf(stderr, "connection error: %s\n", PQerrorMessage(conn));
47 /* Get a list of relations that have OIDs */
49 printfPQExpBuffer(&sql, "%s",
50 "SET search_path = public;"
51 "SELECT c.relname, (SELECT nspname FROM "
52 "pg_catalog.pg_namespace n WHERE n.oid = c.relnamespace) AS nspname "
53 "FROM pg_catalog.pg_class c "
54 "WHERE c.relkind = 'r' "
56 "ORDER BY nspname, c.relname"
59 res = PQexec(conn, sql.data);
60 if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
62 fprintf(stderr, "sql error: %s\n", PQerrorMessage(conn));
67 /* Get a list of columns of OID type (or any OID-alias type) */
69 printfPQExpBuffer(&sql, "%s",
71 "(SELECT nspname FROM pg_catalog.pg_namespace n WHERE n.oid = c.relnamespace) AS nspname, "
73 "FROM pg_catalog.pg_class c, pg_catalog.pg_attribute a "
74 "WHERE a.attnum > 0 AND c.relkind = 'r' "
75 "AND a.attrelid = c.oid "
76 "AND a.atttypid IN ('pg_catalog.oid'::regtype, "
77 " 'pg_catalog.regclass'::regtype, "
78 " 'pg_catalog.regoper'::regtype, "
79 " 'pg_catalog.regoperator'::regtype, "
80 " 'pg_catalog.regproc'::regtype, "
81 " 'pg_catalog.regprocedure'::regtype, "
82 " 'pg_catalog.regtype'::regtype, "
83 " 'pg_catalog.regconfig'::regtype, "
84 " 'pg_catalog.regdictionary'::regtype) "
85 "ORDER BY nspname, c.relname, a.attnum"
88 res = PQexec(conn, sql.data);
89 if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
91 fprintf(stderr, "sql error: %s\n", PQerrorMessage(conn));
97 * For each column and each relation-having-OIDs, look to see if the
98 * column contains any values matching entries in the relation.
101 for (fk = 0; fk < PQntuples(fkrel_res); fk++)
103 fk_relname = PQgetvalue(fkrel_res, fk, 0);
104 fk_nspname = PQgetvalue(fkrel_res, fk, 1);
105 fk_attname = PQgetvalue(fkrel_res, fk, 2);
107 for (pk = 0; pk < PQntuples(pkrel_res); pk++)
109 pk_relname = PQgetvalue(pkrel_res, pk, 0);
110 pk_nspname = PQgetvalue(pkrel_res, pk, 1);
112 printfPQExpBuffer(&sql,
114 "FROM \"%s\".\"%s\" t1, "
116 "WHERE t1.\"%s\"::pg_catalog.oid = t2.oid "
118 fk_nspname, fk_relname,
119 pk_nspname, pk_relname,
122 res = PQexec(conn, sql.data);
123 if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
125 fprintf(stderr, "sql error: %s\n", PQerrorMessage(conn));
129 if (PQntuples(res) != 0)
130 printf("Join %s.%s.%s => %s.%s.oid\n",
131 fk_nspname, fk_relname, fk_attname,
132 pk_nspname, pk_relname);
140 /* Now, do the same for referencing columns that are arrays */
142 /* Get a list of columns of OID-array type (or any OID-alias type) */
144 printfPQExpBuffer(&sql, "%s",
146 "(SELECT nspname FROM pg_catalog.pg_namespace n WHERE n.oid = c.relnamespace) AS nspname, "
148 "FROM pg_catalog.pg_class c, pg_catalog.pg_attribute a "
149 "WHERE a.attnum > 0 AND c.relkind = 'r' "
150 "AND a.attrelid = c.oid "
151 "AND a.atttypid IN ('pg_catalog.oid[]'::regtype, "
152 " 'pg_catalog.regclass[]'::regtype, "
153 " 'pg_catalog.regoper[]'::regtype, "
154 " 'pg_catalog.regoperator[]'::regtype, "
155 " 'pg_catalog.regproc[]'::regtype, "
156 " 'pg_catalog.regprocedure[]'::regtype, "
157 " 'pg_catalog.regtype[]'::regtype, "
158 " 'pg_catalog.regconfig[]'::regtype, "
159 " 'pg_catalog.regdictionary[]'::regtype) "
160 "ORDER BY nspname, c.relname, a.attnum"
163 res = PQexec(conn, sql.data);
164 if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
166 fprintf(stderr, "sql error: %s\n", PQerrorMessage(conn));
172 * For each column and each relation-having-OIDs, look to see if the
173 * column contains any values matching entries in the relation.
176 for (fk = 0; fk < PQntuples(fkrel_res); fk++)
178 fk_relname = PQgetvalue(fkrel_res, fk, 0);
179 fk_nspname = PQgetvalue(fkrel_res, fk, 1);
180 fk_attname = PQgetvalue(fkrel_res, fk, 2);
182 for (pk = 0; pk < PQntuples(pkrel_res); pk++)
184 pk_relname = PQgetvalue(pkrel_res, pk, 0);
185 pk_nspname = PQgetvalue(pkrel_res, pk, 1);
187 printfPQExpBuffer(&sql,
189 "FROM \"%s\".\"%s\" t1, "
191 "WHERE t2.oid = ANY(t1.\"%s\")"
193 fk_nspname, fk_relname,
194 pk_nspname, pk_relname,
197 res = PQexec(conn, sql.data);
198 if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
200 fprintf(stderr, "sql error: %s\n", PQerrorMessage(conn));
204 if (PQntuples(res) != 0)
205 printf("Join %s.%s.%s []=> %s.%s.oid\n",
206 fk_nspname, fk_relname, fk_attname,
207 pk_nspname, pk_relname);
219 termPQExpBuffer(&sql);