PostgreSQL Source Code  git master
 All Data Structures Namespaces Files Functions Variables Typedefs Enumerations Enumerator Macros
findoidjoins.c File Reference
#include "postgres_fe.h"
#include "catalog/pg_class.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

int main ( int  argc,
char **  argv 
)

Definition at line 17 of file findoidjoins.c.

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

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