src/tools/findoidjoins/findoidjoins.c

   1 /*
   2  * findoidjoins.c
   3  *
   4  * Copyright (c) 2002-2009, PostgreSQL Global Development Group
   5  *
   6  * $PostgreSQL$
   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         resetPQExpBuffer(&sql);
  50
  51         appendPQExpBuffer(&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 = 'r' "
  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         resetPQExpBuffer(&sql);
  72
  73         appendPQExpBuffer(&sql, "%s",
  74                                           "SELECT c.relname, "
  75                                           "(SELECT nspname FROM pg_catalog.pg_namespace n WHERE n.oid = c.relnamespace) AS nspname, "
  76                                           "a.attname "
  77                                           "FROM pg_catalog.pg_class c, pg_catalog.pg_attribute a "
  78                                           "WHERE a.attnum > 0 AND c.relkind = 'r' "
  79                                           "AND a.attrelid = c.oid "
  80                                           "AND a.atttypid IN ('pg_catalog.oid'::regtype, "
  81                                           " 'pg_catalog.regclass'::regtype, "
  82                                           " 'pg_catalog.regoper'::regtype, "
  83                                           " 'pg_catalog.regoperator'::regtype, "
  84                                           " 'pg_catalog.regproc'::regtype, "
  85                                           " 'pg_catalog.regprocedure'::regtype, "
  86                                           " 'pg_catalog.regtype'::regtype, "
  87                                           " 'pg_catalog.regconfig'::regtype, "
  88                                           " 'pg_catalog.regdictionary'::regtype) "
  89                                           "ORDER BY nspname, c.relname, a.attnum"
  90                 );
  91
  92         res = PQexec(conn, sql.data);
  93         if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
  94         {
  95                 fprintf(stderr, "sql error:  %s\n", PQerrorMessage(conn));
  96                 exit(EXIT_FAILURE);
  97         }
  98         fkrel_res = res;
  99
 100         /*
 101          * For each column and each relation-having-OIDs, look to see if the
 102          * column contains any values matching entries in the relation.
 103          */
 104
 105         for (fk = 0; fk < PQntuples(fkrel_res); fk++)
 106         {
 107                 fk_relname = PQgetvalue(fkrel_res, fk, 0);
 108                 fk_nspname = PQgetvalue(fkrel_res, fk, 1);
 109                 fk_attname = PQgetvalue(fkrel_res, fk, 2);
 110
 111                 for (pk = 0; pk < PQntuples(pkrel_res); pk++)
 112                 {
 113                         pk_relname = PQgetvalue(pkrel_res, pk, 0);
 114                         pk_nspname = PQgetvalue(pkrel_res, pk, 1);
 115
 116                         resetPQExpBuffer(&sql);
 117
 118                         appendPQExpBuffer(&sql,
 119                                                           "SELECT       1 "
 120                                                           "FROM \"%s\".\"%s\" t1, "
 121                                                           "\"%s\".\"%s\" t2 "
 122                                                           "WHERE t1.\"%s\"::pg_catalog.oid = t2.oid "
 123                                                           "LIMIT 1",
 124                                  fk_nspname, fk_relname, pk_nspname, pk_relname, fk_attname);
 125
 126                         res = PQexec(conn, sql.data);
 127                         if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
 128                         {
 129                                 fprintf(stderr, "sql error:  %s\n", PQerrorMessage(conn));
 130                                 exit(EXIT_FAILURE);
 131                         }
 132
 133                         if (PQntuples(res) != 0)
 134                                 printf("Join %s.%s.%s => %s.%s.oid\n",
 135                                            fk_nspname, fk_relname, fk_attname,
 136                                            pk_nspname, pk_relname);
 137
 138                         PQclear(res);
 139                 }
 140         }
 141
 142         PQclear(pkrel_res);
 143         PQclear(fkrel_res);
 144         PQfinish(conn);
 145
 146         termPQExpBuffer(&sql);
 147
 148         exit(EXIT_SUCCESS);
 149 }