PostgreSQL Source Code  git master
 All Data Structures Namespaces Files Functions Variables Typedefs Enumerations Enumerator Macros
findoidjoins.c
Go to the documentation of this file.
1 /*
2  * findoidjoins.c
3  *
4  * Copyright (c) 2002-2017, PostgreSQL Global Development Group
5  *
6  * src/tools/findoidjoins/findoidjoins.c
7  */
8 #include "postgres_fe.h"
9 
10 #include "libpq-fe.h"
11 #include "pqexpbuffer.h"
12 
13 
14 int
15 main(int argc, char **argv)
16 {
17  PGconn *conn;
18  PQExpBufferData sql;
19  PGresult *res;
20  PGresult *pkrel_res;
21  PGresult *fkrel_res;
22  char *fk_relname;
23  char *fk_nspname;
24  char *fk_attname;
25  char *pk_relname;
26  char *pk_nspname;
27  int fk,
28  pk; /* loop counters */
29 
30  if (argc != 2)
31  {
32  fprintf(stderr, "Usage: %s database\n", argv[0]);
33  exit(EXIT_FAILURE);
34  }
35 
36  initPQExpBuffer(&sql);
37 
38  appendPQExpBuffer(&sql, "dbname=%s", argv[1]);
39 
40  conn = PQconnectdb(sql.data);
41  if (PQstatus(conn) == CONNECTION_BAD)
42  {
43  fprintf(stderr, "connection error: %s\n", PQerrorMessage(conn));
44  exit(EXIT_FAILURE);
45  }
46 
47  /* Get a list of relations that have OIDs */
48 
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' "
55  "AND c.relhasoids "
56  "ORDER BY nspname, c.relname"
57  );
58 
59  res = PQexec(conn, sql.data);
60  if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
61  {
62  fprintf(stderr, "sql error: %s\n", PQerrorMessage(conn));
63  exit(EXIT_FAILURE);
64  }
65  pkrel_res = res;
66 
67  /* Get a list of columns of OID type (or any OID-alias type) */
68 
69  printfPQExpBuffer(&sql, "%s",
70  "SELECT c.relname, "
71  "(SELECT nspname FROM pg_catalog.pg_namespace n WHERE n.oid = c.relnamespace) AS nspname, "
72  "a.attname "
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"
86  );
87 
88  res = PQexec(conn, sql.data);
89  if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
90  {
91  fprintf(stderr, "sql error: %s\n", PQerrorMessage(conn));
92  exit(EXIT_FAILURE);
93  }
94  fkrel_res = res;
95 
96  /*
97  * For each column and each relation-having-OIDs, look to see if the
98  * column contains any values matching entries in the relation.
99  */
100 
101  for (fk = 0; fk < PQntuples(fkrel_res); fk++)
102  {
103  fk_relname = PQgetvalue(fkrel_res, fk, 0);
104  fk_nspname = PQgetvalue(fkrel_res, fk, 1);
105  fk_attname = PQgetvalue(fkrel_res, fk, 2);
106 
107  for (pk = 0; pk < PQntuples(pkrel_res); pk++)
108  {
109  pk_relname = PQgetvalue(pkrel_res, pk, 0);
110  pk_nspname = PQgetvalue(pkrel_res, pk, 1);
111 
112  printfPQExpBuffer(&sql,
113  "SELECT 1 "
114  "FROM \"%s\".\"%s\" t1, "
115  "\"%s\".\"%s\" t2 "
116  "WHERE t1.\"%s\"::pg_catalog.oid = t2.oid "
117  "LIMIT 1",
118  fk_nspname, fk_relname,
119  pk_nspname, pk_relname,
120  fk_attname);
121 
122  res = PQexec(conn, sql.data);
123  if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
124  {
125  fprintf(stderr, "sql error: %s\n", PQerrorMessage(conn));
126  exit(EXIT_FAILURE);
127  }
128 
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);
133 
134  PQclear(res);
135  }
136  }
137 
138  PQclear(fkrel_res);
139 
140  /* Now, do the same for referencing columns that are arrays */
141 
142  /* Get a list of columns of OID-array type (or any OID-alias type) */
143 
144  printfPQExpBuffer(&sql, "%s",
145  "SELECT c.relname, "
146  "(SELECT nspname FROM pg_catalog.pg_namespace n WHERE n.oid = c.relnamespace) AS nspname, "
147  "a.attname "
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"
161  );
162 
163  res = PQexec(conn, sql.data);
164  if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
165  {
166  fprintf(stderr, "sql error: %s\n", PQerrorMessage(conn));
167  exit(EXIT_FAILURE);
168  }
169  fkrel_res = res;
170 
171  /*
172  * For each column and each relation-having-OIDs, look to see if the
173  * column contains any values matching entries in the relation.
174  */
175 
176  for (fk = 0; fk < PQntuples(fkrel_res); fk++)
177  {
178  fk_relname = PQgetvalue(fkrel_res, fk, 0);
179  fk_nspname = PQgetvalue(fkrel_res, fk, 1);
180  fk_attname = PQgetvalue(fkrel_res, fk, 2);
181 
182  for (pk = 0; pk < PQntuples(pkrel_res); pk++)
183  {
184  pk_relname = PQgetvalue(pkrel_res, pk, 0);
185  pk_nspname = PQgetvalue(pkrel_res, pk, 1);
186 
187  printfPQExpBuffer(&sql,
188  "SELECT 1 "
189  "FROM \"%s\".\"%s\" t1, "
190  "\"%s\".\"%s\" t2 "
191  "WHERE t2.oid = ANY(t1.\"%s\")"
192  "LIMIT 1",
193  fk_nspname, fk_relname,
194  pk_nspname, pk_relname,
195  fk_attname);
196 
197  res = PQexec(conn, sql.data);
198  if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
199  {
200  fprintf(stderr, "sql error: %s\n", PQerrorMessage(conn));
201  exit(EXIT_FAILURE);
202  }
203 
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);
208 
209  PQclear(res);
210  }
211  }
212 
213  PQclear(fkrel_res);
214 
215  PQclear(pkrel_res);
216 
217  PQfinish(conn);
218 
219  termPQExpBuffer(&sql);
220 
221  exit(EXIT_SUCCESS);
222 }
char * PQerrorMessage(const PGconn *conn)
Definition: fe-connect.c:5959
void printfPQExpBuffer(PQExpBuffer str, const char *fmt,...)
Definition: pqexpbuffer.c:234
#define EXIT_SUCCESS
Definition: settings.h:146
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:3516
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
void PQclear(PGresult *res)
Definition: fe-exec.c:650
int main(int argc, char **argv)
Definition: findoidjoins.c:15
#define EXIT_FAILURE
Definition: settings.h:150
PGresult * PQexec(PGconn *conn, const char *query)
Definition: fe-exec.c:1846
ConnStatusType PQstatus(const PGconn *conn)
Definition: fe-connect.c:5906
void initPQExpBuffer(PQExpBuffer str)
Definition: pqexpbuffer.c:89
PGconn * PQconnectdb(const char *conninfo)
Definition: fe-connect.c:526