granicus.if.org Git - postgresql/blob - src/tools/findoidjoins/findoidjoins.c

   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.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
  50         res = PQexec(conn, ALWAYS_SECURE_SEARCH_PATH_SQL);
  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 }