PostgreSQL Source Code  git master
findoidjoins.c File Reference
#include "postgres_fe.h"
#include "catalog/pg_class_d.h"
#include "fe_utils/connect.h"
#include "libpq-fe.h"
#include "pqexpbuffer.h"
Include dependency graph for findoidjoins.c:

Go to the source code of this file.

Functions

int main (int argc, char **argv)
 

Function Documentation

◆ main()

int main ( int  argc,
char **  argv 
)

Definition at line 18 of file findoidjoins.c.

References ALWAYS_SECURE_SEARCH_PATH_SQL, appendPQExpBuffer(), conn, CONNECTION_BAD, CppAsString2, PQExpBufferData::data, EXIT_FAILURE, EXIT_SUCCESS, fprintf, initPQExpBuffer(), PGRES_TUPLES_OK, PQclear(), PQconnectdb(), PQerrorMessage(), PQexec(), PQfinish(), PQgetvalue(), PQntuples(), PQresultStatus(), PQstatus(), printf, printfPQExpBuffer(), and termPQExpBuffer().

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 EXISTS(SELECT * FROM pg_attribute a"
67  " WHERE a.attrelid = c.oid AND a.attname = 'oid' "
68  " AND a.atttypid = 'oid'::regtype)"
69  "ORDER BY nspname, c.relname"
70  );
71 
72  res = PQexec(conn, sql.data);
73  if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
74  {
75  fprintf(stderr, "sql error: %s\n", PQerrorMessage(conn));
76  exit(EXIT_FAILURE);
77  }
78  pkrel_res = res;
79 
80  /* Get a list of columns of OID type (or any OID-alias type) */
81 
82  printfPQExpBuffer(&sql, "%s",
83  "SELECT c.relname, "
84  "(SELECT nspname FROM pg_catalog.pg_namespace n WHERE n.oid = c.relnamespace) AS nspname, "
85  "a.attname "
86  "FROM pg_catalog.pg_class c, pg_catalog.pg_attribute a "
87  "WHERE a.attnum > 0"
88  " AND c.relkind = " CppAsString2(RELKIND_RELATION)
89  " AND a.attrelid = c.oid"
90  " AND a.atttypid IN ('pg_catalog.oid'::regtype, "
91  " 'pg_catalog.regclass'::regtype, "
92  " 'pg_catalog.regoper'::regtype, "
93  " 'pg_catalog.regoperator'::regtype, "
94  " 'pg_catalog.regproc'::regtype, "
95  " 'pg_catalog.regprocedure'::regtype, "
96  " 'pg_catalog.regtype'::regtype, "
97  " 'pg_catalog.regconfig'::regtype, "
98  " 'pg_catalog.regdictionary'::regtype) "
99  "ORDER BY nspname, c.relname, a.attnum"
100  );
101 
102  res = PQexec(conn, sql.data);
103  if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
104  {
105  fprintf(stderr, "sql error: %s\n", PQerrorMessage(conn));
106  exit(EXIT_FAILURE);
107  }
108  fkrel_res = res;
109 
110  /*
111  * For each column and each relation-having-OIDs, look to see if the
112  * column contains any values matching entries in the relation.
113  */
114 
115  for (fk = 0; fk < PQntuples(fkrel_res); fk++)
116  {
117  fk_relname = PQgetvalue(fkrel_res, fk, 0);
118  fk_nspname = PQgetvalue(fkrel_res, fk, 1);
119  fk_attname = PQgetvalue(fkrel_res, fk, 2);
120 
121  for (pk = 0; pk < PQntuples(pkrel_res); pk++)
122  {
123  pk_relname = PQgetvalue(pkrel_res, pk, 0);
124  pk_nspname = PQgetvalue(pkrel_res, pk, 1);
125 
126  printfPQExpBuffer(&sql,
127  "SELECT 1 "
128  "FROM \"%s\".\"%s\" t1, "
129  "\"%s\".\"%s\" t2 "
130  "WHERE t1.\"%s\"::pg_catalog.oid = t2.oid "
131  "LIMIT 1",
132  fk_nspname, fk_relname,
133  pk_nspname, pk_relname,
134  fk_attname);
135 
136  res = PQexec(conn, sql.data);
137  if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
138  {
139  fprintf(stderr, "sql error: %s\n", PQerrorMessage(conn));
140  exit(EXIT_FAILURE);
141  }
142 
143  if (PQntuples(res) != 0)
144  printf("Join %s.%s.%s => %s.%s.oid\n",
145  fk_nspname, fk_relname, fk_attname,
146  pk_nspname, pk_relname);
147 
148  PQclear(res);
149  }
150  }
151 
152  PQclear(fkrel_res);
153 
154  /* Now, do the same for referencing columns that are arrays */
155 
156  /* Get a list of columns of OID-array type (or any OID-alias type) */
157 
158  printfPQExpBuffer(&sql, "%s",
159  "SELECT c.relname, "
160  "(SELECT nspname FROM pg_catalog.pg_namespace n WHERE n.oid = c.relnamespace) AS nspname, "
161  "a.attname "
162  "FROM pg_catalog.pg_class c, pg_catalog.pg_attribute a "
163  "WHERE a.attnum > 0"
164  " AND c.relkind = " CppAsString2(RELKIND_RELATION)
165  " AND a.attrelid = c.oid"
166  " AND a.atttypid IN ('pg_catalog.oid[]'::regtype, "
167  " 'pg_catalog.regclass[]'::regtype, "
168  " 'pg_catalog.regoper[]'::regtype, "
169  " 'pg_catalog.regoperator[]'::regtype, "
170  " 'pg_catalog.regproc[]'::regtype, "
171  " 'pg_catalog.regprocedure[]'::regtype, "
172  " 'pg_catalog.regtype[]'::regtype, "
173  " 'pg_catalog.regconfig[]'::regtype, "
174  " 'pg_catalog.regdictionary[]'::regtype) "
175  "ORDER BY nspname, c.relname, a.attnum"
176  );
177 
178  res = PQexec(conn, sql.data);
179  if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
180  {
181  fprintf(stderr, "sql error: %s\n", PQerrorMessage(conn));
182  exit(EXIT_FAILURE);
183  }
184  fkrel_res = res;
185 
186  /*
187  * For each column and each relation-having-OIDs, look to see if the
188  * column contains any values matching entries in the relation.
189  */
190 
191  for (fk = 0; fk < PQntuples(fkrel_res); fk++)
192  {
193  fk_relname = PQgetvalue(fkrel_res, fk, 0);
194  fk_nspname = PQgetvalue(fkrel_res, fk, 1);
195  fk_attname = PQgetvalue(fkrel_res, fk, 2);
196 
197  for (pk = 0; pk < PQntuples(pkrel_res); pk++)
198  {
199  pk_relname = PQgetvalue(pkrel_res, pk, 0);
200  pk_nspname = PQgetvalue(pkrel_res, pk, 1);
201 
202  printfPQExpBuffer(&sql,
203  "SELECT 1 "
204  "FROM \"%s\".\"%s\" t1, "
205  "\"%s\".\"%s\" t2 "
206  "WHERE t2.oid = ANY(t1.\"%s\")"
207  "LIMIT 1",
208  fk_nspname, fk_relname,
209  pk_nspname, pk_relname,
210  fk_attname);
211 
212  res = PQexec(conn, sql.data);
213  if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
214  {
215  fprintf(stderr, "sql error: %s\n", PQerrorMessage(conn));
216  exit(EXIT_FAILURE);
217  }
218 
219  if (PQntuples(res) != 0)
220  printf("Join %s.%s.%s []=> %s.%s.oid\n",
221  fk_nspname, fk_relname, fk_attname,
222  pk_nspname, pk_relname);
223 
224  PQclear(res);
225  }
226  }
227 
228  PQclear(fkrel_res);
229 
230  PQclear(pkrel_res);
231 
232  PQfinish(conn);
233 
234  termPQExpBuffer(&sql);
235 
236  exit(EXIT_SUCCESS);
237 }
char * PQerrorMessage(const PGconn *conn)
Definition: fe-connect.c:6623
void printfPQExpBuffer(PQExpBuffer str, const char *fmt,...)
Definition: pqexpbuffer.c:237
#define EXIT_SUCCESS
Definition: settings.h:149
char * PQgetvalue(const PGresult *res, int tup_num, int field_num)
Definition: fe-exec.c:3163
void termPQExpBuffer(PQExpBuffer str)
Definition: pqexpbuffer.c:131
void PQfinish(PGconn *conn)
Definition: fe-connect.c:4125
#define printf(...)
Definition: port.h:198
int PQntuples(const PGresult *res)
Definition: fe-exec.c:2769
#define fprintf
Definition: port.h:196
ExecStatusType PQresultStatus(const PGresult *res)
Definition: fe-exec.c:2692
PGconn * conn
Definition: streamutil.c:54
void appendPQExpBuffer(PQExpBuffer str, const char *fmt,...)
Definition: pqexpbuffer.c:267
#define CppAsString2(x)
Definition: c.h:224
void PQclear(PGresult *res)
Definition: fe-exec.c:694
#define ALWAYS_SECURE_SEARCH_PATH_SQL
Definition: connect.h:25
#define EXIT_FAILURE
Definition: settings.h:153
PGresult * PQexec(PGconn *conn, const char *query)
Definition: fe-exec.c:1939
ConnStatusType PQstatus(const PGconn *conn)
Definition: fe-connect.c:6570
void initPQExpBuffer(PQExpBuffer str)
Definition: pqexpbuffer.c:92
PGconn * PQconnectdb(const char *conninfo)
Definition: fe-connect.c:680