PostgreSQL Source Code  git master
findoidjoins.c
Go to the documentation of this file.
1 /*
2  * findoidjoins.c
3  *
4  * Copyright (c) 2002-2018, PostgreSQL Global Development Group
5  *
6  * src/tools/findoidjoins/findoidjoins.c
7  */
8 #include "postgres_fe.h"
9 
10 #include "catalog/pg_class_d.h"
11 
12 #include "fe_utils/connect.h"
13 #include "libpq-fe.h"
14 #include "pqexpbuffer.h"
15 
16 
17 int
18 main(int argc, char **argv)
19 {
20  PGconn *conn;
21  PQExpBufferData sql;
22  PGresult *res;
23  PGresult *pkrel_res;
24  PGresult *fkrel_res;
25  char *fk_relname;
26  char *fk_nspname;
27  char *fk_attname;
28  char *pk_relname;
29  char *pk_nspname;
30  int fk,
31  pk; /* loop counters */
32 
33  if (argc != 2)
34  {
35  fprintf(stderr, "Usage: %s database\n", argv[0]);
36  exit(EXIT_FAILURE);
37  }
38 
39  initPQExpBuffer(&sql);
40 
41  appendPQExpBuffer(&sql, "dbname=%s", argv[1]);
42 
43  conn = PQconnectdb(sql.data);
44  if (PQstatus(conn) == CONNECTION_BAD)
45  {
46  fprintf(stderr, "connection error: %s\n", PQerrorMessage(conn));
47  exit(EXIT_FAILURE);
48  }
49 
51  if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
52  {
53  fprintf(stderr, "sql error: %s\n", PQerrorMessage(conn));
54  exit(EXIT_FAILURE);
55  }
56  PQclear(res);
57 
58  /* Get a list of relations that have OIDs */
59 
60  printfPQExpBuffer(&sql, "%s",
61  "SET search_path = public;"
62  "SELECT c.relname, (SELECT nspname FROM "
63  "pg_catalog.pg_namespace n WHERE n.oid = c.relnamespace) AS nspname "
64  "FROM pg_catalog.pg_class c "
65  "WHERE c.relkind = " CppAsString2(RELKIND_RELATION)
66  " AND c.relhasoids "
67  "ORDER BY nspname, c.relname"
68  );
69 
70  res = PQexec(conn, sql.data);
71  if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
72  {
73  fprintf(stderr, "sql error: %s\n", PQerrorMessage(conn));
74  exit(EXIT_FAILURE);
75  }
76  pkrel_res = res;
77 
78  /* Get a list of columns of OID type (or any OID-alias type) */
79 
80  printfPQExpBuffer(&sql, "%s",
81  "SELECT c.relname, "
82  "(SELECT nspname FROM pg_catalog.pg_namespace n WHERE n.oid = c.relnamespace) AS nspname, "
83  "a.attname "
84  "FROM pg_catalog.pg_class c, pg_catalog.pg_attribute a "
85  "WHERE a.attnum > 0"
86  " AND c.relkind = " CppAsString2(RELKIND_RELATION)
87  " AND a.attrelid = c.oid"
88  " AND a.atttypid IN ('pg_catalog.oid'::regtype, "
89  " 'pg_catalog.regclass'::regtype, "
90  " 'pg_catalog.regoper'::regtype, "
91  " 'pg_catalog.regoperator'::regtype, "
92  " 'pg_catalog.regproc'::regtype, "
93  " 'pg_catalog.regprocedure'::regtype, "
94  " 'pg_catalog.regtype'::regtype, "
95  " 'pg_catalog.regconfig'::regtype, "
96  " 'pg_catalog.regdictionary'::regtype) "
97  "ORDER BY nspname, c.relname, a.attnum"
98  );
99 
100  res = PQexec(conn, sql.data);
101  if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
102  {
103  fprintf(stderr, "sql error: %s\n", PQerrorMessage(conn));
104  exit(EXIT_FAILURE);
105  }
106  fkrel_res = res;
107 
108  /*
109  * For each column and each relation-having-OIDs, look to see if the
110  * column contains any values matching entries in the relation.
111  */
112 
113  for (fk = 0; fk < PQntuples(fkrel_res); fk++)
114  {
115  fk_relname = PQgetvalue(fkrel_res, fk, 0);
116  fk_nspname = PQgetvalue(fkrel_res, fk, 1);
117  fk_attname = PQgetvalue(fkrel_res, fk, 2);
118 
119  for (pk = 0; pk < PQntuples(pkrel_res); pk++)
120  {
121  pk_relname = PQgetvalue(pkrel_res, pk, 0);
122  pk_nspname = PQgetvalue(pkrel_res, pk, 1);
123 
124  printfPQExpBuffer(&sql,
125  "SELECT 1 "
126  "FROM \"%s\".\"%s\" t1, "
127  "\"%s\".\"%s\" t2 "
128  "WHERE t1.\"%s\"::pg_catalog.oid = t2.oid "
129  "LIMIT 1",
130  fk_nspname, fk_relname,
131  pk_nspname, pk_relname,
132  fk_attname);
133 
134  res = PQexec(conn, sql.data);
135  if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
136  {
137  fprintf(stderr, "sql error: %s\n", PQerrorMessage(conn));
138  exit(EXIT_FAILURE);
139  }
140 
141  if (PQntuples(res) != 0)
142  printf("Join %s.%s.%s => %s.%s.oid\n",
143  fk_nspname, fk_relname, fk_attname,
144  pk_nspname, pk_relname);
145 
146  PQclear(res);
147  }
148  }
149 
150  PQclear(fkrel_res);
151 
152  /* Now, do the same for referencing columns that are arrays */
153 
154  /* Get a list of columns of OID-array type (or any OID-alias type) */
155 
156  printfPQExpBuffer(&sql, "%s",
157  "SELECT c.relname, "
158  "(SELECT nspname FROM pg_catalog.pg_namespace n WHERE n.oid = c.relnamespace) AS nspname, "
159  "a.attname "
160  "FROM pg_catalog.pg_class c, pg_catalog.pg_attribute a "
161  "WHERE a.attnum > 0"
162  " AND c.relkind = " CppAsString2(RELKIND_RELATION)
163  " AND a.attrelid = c.oid"
164  " AND a.atttypid IN ('pg_catalog.oid[]'::regtype, "
165  " 'pg_catalog.regclass[]'::regtype, "
166  " 'pg_catalog.regoper[]'::regtype, "
167  " 'pg_catalog.regoperator[]'::regtype, "
168  " 'pg_catalog.regproc[]'::regtype, "
169  " 'pg_catalog.regprocedure[]'::regtype, "
170  " 'pg_catalog.regtype[]'::regtype, "
171  " 'pg_catalog.regconfig[]'::regtype, "
172  " 'pg_catalog.regdictionary[]'::regtype) "
173  "ORDER BY nspname, c.relname, a.attnum"
174  );
175 
176  res = PQexec(conn, sql.data);
177  if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
178  {
179  fprintf(stderr, "sql error: %s\n", PQerrorMessage(conn));
180  exit(EXIT_FAILURE);
181  }
182  fkrel_res = res;
183 
184  /*
185  * For each column and each relation-having-OIDs, look to see if the
186  * column contains any values matching entries in the relation.
187  */
188 
189  for (fk = 0; fk < PQntuples(fkrel_res); fk++)
190  {
191  fk_relname = PQgetvalue(fkrel_res, fk, 0);
192  fk_nspname = PQgetvalue(fkrel_res, fk, 1);
193  fk_attname = PQgetvalue(fkrel_res, fk, 2);
194 
195  for (pk = 0; pk < PQntuples(pkrel_res); pk++)
196  {
197  pk_relname = PQgetvalue(pkrel_res, pk, 0);
198  pk_nspname = PQgetvalue(pkrel_res, pk, 1);
199 
200  printfPQExpBuffer(&sql,
201  "SELECT 1 "
202  "FROM \"%s\".\"%s\" t1, "
203  "\"%s\".\"%s\" t2 "
204  "WHERE t2.oid = ANY(t1.\"%s\")"
205  "LIMIT 1",
206  fk_nspname, fk_relname,
207  pk_nspname, pk_relname,
208  fk_attname);
209 
210  res = PQexec(conn, sql.data);
211  if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
212  {
213  fprintf(stderr, "sql error: %s\n", PQerrorMessage(conn));
214  exit(EXIT_FAILURE);
215  }
216 
217  if (PQntuples(res) != 0)
218  printf("Join %s.%s.%s []=> %s.%s.oid\n",
219  fk_nspname, fk_relname, fk_attname,
220  pk_nspname, pk_relname);
221 
222  PQclear(res);
223  }
224  }
225 
226  PQclear(fkrel_res);
227 
228  PQclear(pkrel_res);
229 
230  PQfinish(conn);
231 
232  termPQExpBuffer(&sql);
233 
234  exit(EXIT_SUCCESS);
235 }
char * PQerrorMessage(const PGconn *conn)
Definition: fe-connect.c:6116
void printfPQExpBuffer(PQExpBuffer str, const char *fmt,...)
Definition: pqexpbuffer.c:234
#define EXIT_SUCCESS
Definition: settings.h:148
char * PQgetvalue(const PGresult *res, int tup_num, int field_num)
Definition: fe-exec.c:3118
void termPQExpBuffer(PQExpBuffer str)
Definition: pqexpbuffer.c:128
void PQfinish(PGconn *conn)
Definition: fe-connect.c:3638
int PQntuples(const PGresult *res)
Definition: fe-exec.c:2724
ExecStatusType PQresultStatus(const PGresult *res)
Definition: fe-exec.c:2647
PGconn * conn
Definition: streamutil.c:55
void appendPQExpBuffer(PQExpBuffer str, const char *fmt,...)
Definition: pqexpbuffer.c:262
#define CppAsString2(x)
Definition: c.h:224
void PQclear(PGresult *res)
Definition: fe-exec.c:671
int main(int argc, char **argv)
Definition: findoidjoins.c:18
#define ALWAYS_SECURE_SEARCH_PATH_SQL
Definition: connect.h:25
#define EXIT_FAILURE
Definition: settings.h:152
PGresult * PQexec(PGconn *conn, const char *query)
Definition: fe-exec.c:1897
ConnStatusType PQstatus(const PGconn *conn)
Definition: fe-connect.c:6063
void initPQExpBuffer(PQExpBuffer str)
Definition: pqexpbuffer.c:89
PGconn * PQconnectdb(const char *conninfo)
Definition: fe-connect.c:592