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

   1 /*
   2  * findoidjoins.c
   3  *
   4  * Copyright (c) 2002-2014, 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 }