contrib/findoidjoins/findoidjoins.c

   1 /*
   2  * findoidjoins.c, required pgsql/contrib/pginterface
   3  *
   4  */
   5
   6 #include <stdio.h>
   7 #include <string.h>
   8 #include "halt.h"
   9 #include <libpq-fe.h>
  10 #include "pginterface.h"
  11
  12 PGresult   *attres,
  13                    *relres;
  14
  15 int
  16 main(int argc, char **argv)
  17 {
  18         char            query[4000];
  19         char            relname[256];
  20         char            relname2[256];
  21         char            attname[256];
  22         char            typname[256];
  23         int                     count;
  24
  25         if (argc != 2)
  26                 halt("Usage:  %s database\n", argv[0]);
  27
  28         connectdb(argv[1], NULL, NULL, NULL, NULL);
  29         on_error_continue();
  30         on_error_stop();
  31
  32         doquery("BEGIN WORK");
  33         doquery("\
  34                 DECLARE c_attributes BINARY CURSOR FOR \
  35                 SELECT typname, relname, a.attname \
  36                 FROM pg_class c, pg_attribute a, pg_type t \
  37                 WHERE a.attnum > 0 AND \
  38                           relkind = 'r' AND \
  39                           relhasrules = 'f' AND \
  40                           (typname = 'oid' OR \
  41                            typname = 'regproc') AND \
  42                           a.attrelid = c.oid AND \
  43                           a.atttypid = t.oid \
  44                 ORDER BY 2, a.attnum ; \
  45                 ");
  46         doquery("FETCH ALL IN c_attributes");
  47         attres = get_result();
  48
  49         doquery("\
  50                 DECLARE c_relations BINARY CURSOR FOR \
  51                 SELECT relname \
  52                 FROM pg_class c \
  53                 WHERE relkind = 'r' AND \
  54                           relhasrules = 'f' \
  55                 ORDER BY 1; \
  56                 ");
  57         doquery("FETCH ALL IN c_relations");
  58         relres = get_result();
  59
  60         set_result(attres);
  61         while (fetch(typname, relname, attname) != END_OF_TUPLES)
  62         {
  63                 set_result(relres);
  64                 reset_fetch();
  65                 while (fetch(relname2) != END_OF_TUPLES)
  66                 {
  67                         unset_result(relres);
  68                         if (strcmp(typname, "oid") == 0)
  69                                 sprintf(query, "\
  70                                         DECLARE c_matches BINARY CURSOR FOR \
  71                                         SELECT  count(*) \
  72                                                 FROM % s t1, %s t2 \
  73                                         WHERE t1.% s = t2.oid ", relname, relname2, attname);
  74                         else
  75                                 sprintf(query, "\
  76                                         DECLARE c_matches BINARY CURSOR FOR \
  77                                         SELECT  count(*) \
  78                                                                 FROM % s t1, %s t2 \
  79                                                                 WHERE RegprocToOid(t1.% s) = t2.oid ", relname, relname2, attname);
  80
  81                         doquery(query);
  82                         doquery("FETCH ALL IN c_matches");
  83                         fetch(&count);
  84                         if (count != 0)
  85                                 printf("Join %s.%s => %s.oid\n", relname, attname, relname2);
  86                         doquery("CLOSE c_matches");
  87                         set_result(relres);
  88                 }
  89                 set_result(attres);
  90         }
  91
  92         set_result(relres);
  93         doquery("CLOSE c_relations");
  94         PQclear(relres);
  95
  96         set_result(attres);
  97         doquery("CLOSE c_attributes");
  98         PQclear(attres);
  99         unset_result(attres);
 100
 101         doquery("COMMIT WORK");
 102
 103         disconnectdb();
 104         return 0;
 105 }