Patchers,

Here is a patch bringing oid2name into the 21st century.  Supports
schemas (thought you can't select what schema to display), tablespaces,
and using -o for OIDs and -f for filenames (pg_class.relfilenode).  Also
-t now accepts LIKE patterns.  There can be multiple and mixed -o, -f
and -t arguments, and it will show all the objects in one go.

By default it only shows filenode and relation name, though a -x switch
makes it include tablespace name, schema name and OID.

Also, the headers are prettier.

Please comment, review and/or apply.

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Cuando no hay humildad las personas se degradan" (A. Christie)
Index: README.oid2name
===================================================================
RCS file: /home/alvherre/cvs/pgsql-server/contrib/oid2name/README.oid2name,v
retrieving revision 1.9
diff -c -r1.9 README.oid2name
*** README.oid2name     12 Oct 2002 19:15:09 -0000      1.9
--- README.oid2name     14 Sep 2004 18:31:34 -0000
***************
*** 1,124 ****
  This utility allows administrators to view the file structure used by
! PostgreSQL.  Databases are placed in directories based on their OIDs in
! pg_database, and the tables in that directory are named by original
! OIDs, stored in pg_class.relfilenode.  Oid2name connects to the database
! and extracts the OID and table name information.
  
! ---------------------------------------------------------------------------
! 
! It can be used in four ways:
! 
! 
! oid2name
! 
!   This will connect to the template1 database and display all databases
!   in the system:
! 
!   $ oid2name
!   All databases:
!   ---------------------------------
!   18720  = test1
!   1      = template1
!   18719  = template0
!   18721  = test
!   18735  = postgres
!   18736  = cssi
! 
! 
! oid2name -d test [-x]
! 
!   This connects to the database test and shows all tables and their OIDs:
! 
!   $ oid2name -d test
!   All tables from database "test":
!   ---------------------------------
!   18766  = dns
!   18737  = ips
!   18722  = testdate
! 
! 
! oid2name -d test -o 18737
! oid2name -d test -t testdate
! 
!   This will connect to the database test and display the table name for oid
!   18737 and the oid for table name testdate respectively:
  
!   $ oid2name -d test -o 18737
!   Tablename of oid 18737 from database "test":
!   ---------------------------------
!   18737  = ips
! 
! 
!   $ oid2name -d test -t testdate 
!   Oid of table testdate from database "test":
!   ---------------------------------
!   18722  = testdate
! 
! Keep in mind tables over one gigabyte will be split into separate files
! with numeric file extensions.
  
  ---------------------------------------------------------------------------
  
  Sample session:
  
- $ cd /u/pg/data/base
  $ oid2name
  All databases:
! ---------------------------------
! 16817  = test2
! 16578  = x
! 16756  = test
! 1      = template1
! 16569  = template0
! 16818  = test3
! 16811  = floattest
! 
! $ cd 16756
! $ ls 1873*
! 18730   18731   18732   18735   18736   18737   18738   18739
! 
! $ oid2name -d test -o 18737
! Tablename of oid 18737 from database "test":
! ---------------------------------
! 18737  = ips
! 
! $ oid2name -d test -t ips
! Oid of table ips from database "test":
! ---------------------------------
! 18737  = ips
  
  $ # show disk space for every db object
! $ du * | while read SIZE OID
  > do
! >     echo "$SIZE      `oid2name -q -d test -o $OID`"
  > done
! 24      18737  = ips
! 36      18722  = cities
  ...
  
! $ # same as above, but sort by largest first
! $ du * | while read SIZE OID
  > do
! >     echo "$SIZE      `oid2name -q -d test -o $OID`"
! > done |
! > sort -rn
! 2048    19324  = bigtable
! 1950    23903  = customers
  ...
  
! $ # show disk usage per database
! $ cd /u/pg/data/base
! $ du -s * |
! > while read SIZE OID
! > do
! >     echo "$SIZE      `aspg oid2name -q | grep ^$OID' '`"
! > done |
! > sort -rn
! 2256        18721  = test
! 2135        18735  = postgres
! ..
  
! This can be done in psql with:  
  
  test=> SELECT relpages, relfilenode, relname FROM pg_class ORDER BY relpages DESC;
  
--- 1,170 ----
  This utility allows administrators to view the file structure used by
! PostgreSQL.
  
! Databases are placed in directories based on their OIDs in pg_database,
! and the tables in that directory are named by original OIDs, stored in
! pg_class.relfilenode.
! 
! Tablespaces make the scenario more complicated.  Each tablespace has a
! directory inside the pg_tblspc directory, with a symlink pointing to the
! actual tablespace directory.  Inside that directory there is another
! directory for each database that has elements in the tablespace, named
! after the database's OID.
! 
! Oid2name connects to the database and extracts the OID and table name
! information.  You can also have it show the database OIDs and tablespace
! OIDs.
! 
! When displaying tables (i.e. not all databases, not tablespaces, and not
! a whole database) you can select which tables to show by using -o, -f
! and -t.  The first switch receives an OID, the second receives a
! filename, and the third receives a tablename (actually, it's a LIKE
! pattern, so you can use things like "foo%").  Note that you can use as
! many of these switches as you like, and the listing will include all
! relevant objects.
! 
! Note that while OID and filename sometimes match, this is not
! necessarily the case; some operations, like TRUNCATE, REINDEX, CLUSTER
! and some forms of ALTER TABLE can change the filename while preserving
! the OID.
! 
! Additional switches:
!       -i      include indexes and sequences in the database listing.
!       -x      display more information about each object shown:
!               tablespace name, schema name, OID.
!       -S      also show system objects
!               (those in information_schema, pg_toast and pg_catalog schemas)
!       -q      don't display headers
!               (useful for scripting)
!       -s      show a tablespace listing instead
  
! If you don't give any of -t, -f or -o it will dump the whole database
! given in -d.  If you don't give -d, it will show a database listing.
  
  ---------------------------------------------------------------------------
  
  Sample session:
  
  $ oid2name
  All databases:
!     Oid  Database Name  Tablespace
! ----------------------------------
!   17228       alvherre  pg_default
!   17255     regression  pg_default
!   17227      template0  pg_default
!       1      template1  pg_default
! 
! $ oid2name -s
! All tablespaces:
!      Oid  Tablespace Name
! -------------------------
!     1663       pg_default
!     1664        pg_global
!   155151         fastdisk
!   155152          bigdisk
! 
! $ cd $PGDATA/17228
! 
! $ # get top 10 db objects in the default tablespace, ordered by size
! $ ls -lS * | head -10
! -rw-------  1 alvherre alvherre 136536064 sep 14 09:51 155173
! -rw-------  1 alvherre alvherre  17965056 sep 14 09:51 1155291
! -rw-------  1 alvherre alvherre   1204224 sep 14 09:51 16717
! -rw-------  1 alvherre alvherre    581632 sep  6 17:51 1255
! -rw-------  1 alvherre alvherre    237568 sep 14 09:50 16674
! -rw-------  1 alvherre alvherre    212992 sep 14 09:51 1249
! -rw-------  1 alvherre alvherre    204800 sep 14 09:51 16684
! -rw-------  1 alvherre alvherre    196608 sep 14 09:50 16700
! -rw-------  1 alvherre alvherre    163840 sep 14 09:50 16699
! -rw-------  1 alvherre alvherre    122880 sep  6 17:51 16751
! 
! $ oid2name -d alvherre -f 155173
! From database "alvherre":
!   Filenode  Table Name
! ----------------------
!     155173    accounts
! 
! $ # you can ask for more than one object
! $ oid2name -d alvherre -f 155173 -f 1155291
! From database "alvherre":
!   Filenode     Table Name
! -------------------------
!     155173       accounts
!    1155291  accounts_pkey
! 
! $ # you can also mix the options, and have more details
! $ oid2name -d alvherre -t accounts -f 1155291 -x
! From database "alvherre":
!   Filenode     Table Name      Oid  Schema  Tablespace
! ------------------------------------------------------
!     155173       accounts   155173  public  pg_default
!    1155291  accounts_pkey  1155291  public  pg_default
  
  $ # show disk space for every db object
! $ du [0-9]* |
! > while read SIZE OID
  > do
! >   echo "$SIZE       `oid2name -q -d alvherre -i -f $OID`"
  > done
! 16       1155287  branches_pkey
! 16       1155289  tellers_pkey
! 17561            1155291  accounts_pkey
  ...
  
! $ # same, but sort by size
! $ du [0-9]* | sort -rn | while read SIZE FN
  > do
! >   echo "$SIZE   `oid2name -q -d alvherre -f $FN`"
! > done
! 133466            155173    accounts
! 17561            1155291  accounts_pkey
! 1177       16717  pg_proc_proname_args_nsp_index
  ...
  
! $ # If you want to see what's in tablespaces, use the pg_tblspc directory
! $ cd $PGDATA/pg_tblspc
! $ oid2name -s
! All tablespaces:
!      Oid  Tablespace Name
! -------------------------
!     1663       pg_default
!     1664        pg_global
!   155151         fastdisk
!   155152          bigdisk
! 
! $ # what databases have objects in tablespace "fastdisk"?
! $ ls -d 155151/*
! 155151/17228/  155151/PG_VERSION
! 
! $ # Oh, what was database 17228 again?
! $ oid2name   
! All databases:
!     Oid  Database Name  Tablespace
! ----------------------------------
!   17228       alvherre  pg_default
!   17255     regression  pg_default
!   17227      template0  pg_default
!       1      template1  pg_default
! 
! $ # Let's see what objects does this database has in the tablespace.
! $ cd 155151/17228
! $ ls -l
! total 0
! -rw-------  1 postgres postgres 0 sep 13 23:20 155156
! 
! $ # OK, this is a pretty small table ... but which one is it?
! $ oid2name -d alvherre -f 155156
! From database "alvherre":
!   Filenode  Table Name
! ----------------------
!     155156         foo
! 
! $ # end of sample session.
! 
! ---------------------------------------------------------------------------
  
! You can also get approximate size data for each object using psql.  For
! example,
  
  test=> SELECT relpages, relfilenode, relname FROM pg_class ORDER BY relpages DESC;
  
Index: oid2name.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql-server/contrib/oid2name/oid2name.c,v
retrieving revision 1.23
diff -c -r1.23 oid2name.c
*** oid2name.c  3 Mar 2004 14:24:12 -0000       1.23
--- oid2name.c  14 Sep 2004 18:46:37 -0000
***************
*** 1,9 ****
  /*
!   oid2name; a postgresql 7.1 (+?) app to map OIDs on the filesystem
!    to table and database names.
! 
!   b. palmer, [EMAIL PROTECTED] 1-17-2001
! 
   */
  #include "postgres_fe.h"
  
--- 1,9 ----
  /*
!  * oid2name, a PostgreSQL app to map OIDs on the filesystem
!  * to table and database names.
!  *
!  * Originally by
!  * B. Palmer, [EMAIL PROTECTED] 1-17-2001
   */
  #include "postgres_fe.h"
  
***************
*** 14,54 ****
  
  #include "libpq-fe.h"
  
  /* these are the opts structures for command line params */
  struct options
  {
!       int                     getdatabase;
!       int                     gettable;
!       int                     getoid;
! 
!       int                     quiet;
! 
!       int                     systables;
! 
!       int                     remotehost;
!       int                     remoteport;
!       int                     remoteuser;
!       int                     remotepass;
! 
!       int                     _oid;
!       char            _dbname[128];
!       char            _tbname[128];
! 
!       char            _hostname[128];
!       char            _port[6];
!       char            _username[128];
!       char            _password[128];
  };
  
  /* function prototypes */
  void          get_opts(int, char **, struct options *);
! PGconn           *sql_conn(const char *, struct options *);
! void          sql_exec_error(int);
! int                   sql_exec(PGconn *, const char *, int);
! void          sql_exec_dumpdb(PGconn *);
! void          sql_exec_dumptable(PGconn *, int);
! void          sql_exec_searchtable(PGconn *, const char *);
! void          sql_exec_searchoid(PGconn *, int);
  
  /* function to parse command line options and check for some usage errors. */
  void
--- 14,59 ----
  
  #include "libpq-fe.h"
  
+ /* an extensible array to keep track of elements to show */
+ typedef struct
+ {
+       char  **array;
+       int             num;
+       int             alloc;
+ } eary;
+ 
  /* these are the opts structures for command line params */
  struct options
  {
!       eary       *tables;
!       eary       *oids;
!       eary       *filenodes;
! 
!       bool            quiet;
!       bool            systables;
!       bool            indexes;
!       bool            nodb;
!       bool            extended;
!       bool            tablespaces;
! 
!       char            *dbname;
!       char            *hostname;
!       char            *port;
!       char            *username;
!       char            *password;
  };
  
  /* function prototypes */
  void          get_opts(int, char **, struct options *);
! void     *myalloc(size_t size);
! void          add_one_elt(char *eltname, eary *eary);
! char     *get_comma_elts(eary *eary);
! PGconn           *sql_conn(struct options *);
! int                   sql_exec(PGconn *, const char *sql, bool quiet);
! void          sql_exec_dumpalldbs(PGconn *, struct options *);
! void          sql_exec_dumpalltables(PGconn *, struct options *);
! void          sql_exec_searchtables(PGconn *, struct options *);
! void          sql_exec_dumpalltbspc(PGconn *, struct options *);
  
  /* function to parse command line options and check for some usage errors. */
  void
***************
*** 57,295 ****
        int                     c;
  
        /* set the defaults */
!       my_opts->getdatabase = 0;
!       my_opts->gettable = 0;
!       my_opts->getoid = 0;
! 
!       my_opts->quiet = 0;
! 
!       my_opts->systables = 0;
! 
!       my_opts->remotehost = 0;
!       my_opts->remoteport = 0;
!       my_opts->remoteuser = 0;
!       my_opts->remotepass = 0;
  
        /* get opts */
!       while ((c = getopt(argc, argv, "H:p:U:P:d:t:o:qxh?")) != -1)
        {
                switch (c)
                {
                                /* specify the database */
                        case 'd':
!                               my_opts->getdatabase = 1;
!                               sscanf(optarg, "%s", my_opts->_dbname);
                                break;
  
!                               /* specify the table name */
                        case 't':
!                               /* make sure we set the database first */
!                               if (!my_opts->getdatabase)
!                               {
!                                       fprintf(stderr, "You must specify a database 
to dump from.\n");
!                                       exit(1);
!                               }
!                               /* make sure we don't try to do a -o also */
!                               if (my_opts->getoid)
!                               {
!                                       fprintf(stderr, "You can only specify either 
oid or table\n");
!                                       exit(1);
!                               }
! 
!                               my_opts->gettable = 1;
!                               sscanf(optarg, "%s", my_opts->_tbname);
! 
                                break;
  
!                               /* specify the oid int */
                        case 'o':
!                               /* make sure we set the database first */
!                               if (!my_opts->getdatabase)
!                               {
!                                       fprintf(stderr, "You must specify a database 
to dump from.\n");
!                                       exit(1);
!                               }
!                               /* make sure we don't try to do a -t also */
!                               if (my_opts->gettable)
!                               {
!                                       fprintf(stderr, "You can only specify either 
oid or table\n");
!                                       exit(1);
!                               }
! 
!                               my_opts->getoid = 1;
!                               sscanf(optarg, "%i", &my_opts->_oid);
  
                                break;
  
                        case 'q':
!                               my_opts->quiet = 1;
                                break;
  
                                /* host to connect to */
                        case 'H':
!                               my_opts->remotehost = 1;
!                               sscanf(optarg, "%s", my_opts->_hostname);
                                break;
  
                                /* port to connect to on remote host */
                        case 'p':
!                               my_opts->remoteport = 1;
!                               sscanf(optarg, "%s", my_opts->_port);
                                break;
  
                                /* username */
                        case 'U':
!                               my_opts->remoteuser = 1;
!                               sscanf(optarg, "%s", my_opts->_username);
                                break;
  
                                /* password */
                        case 'P':
!                               my_opts->remotepass = 1;
!                               sscanf(optarg, "%s", my_opts->_password);
                                break;
  
                                /* display system tables */
                        case 'x':
!                               my_opts->systables = 1;
                                break;
  
                                /* help! (ugly in code for easier editing) */
                        case '?':
                        case 'h':
!                               fprintf(stderr, "\
! Usage: oid2name [-d database [-x] ] [-t table | -o oid]\n\
!         default action        display all databases\n\
!         -d database           database to oid2name\n\
!         -x                    display system tables\n\
!         -t table | -o oid     search for table name (-t) or\n\
!                                oid (-o) in -d database\n\
!         -q                    quiet\n\
!         -H host               connect to remote host\n\
!         -p port               host port to connect to\n\
!         -U username           username to connect with\n\
!         -P password           password for username\n\
! ");
                                exit(1);
                                break;
                }
        }
  }
  
! /* establish connection with database. */
! PGconn *
! sql_conn(const char *dbName, struct options * my_opts)
  {
!       char       *pghost,
!                          *pgport;
!       char       *pgoptions,
!                          *pgtty;
!       char       *pguser,
!                          *pgpass;
! 
!       PGconn     *conn;
! 
!       pghost = NULL;
!       pgport = NULL;
!       pgoptions = NULL;                       /* special options to start up the 
backend
!                                                                * server */
!       pgtty = NULL;                           /* debugging tty for the backend 
server */
!       pguser = NULL;
!       pgpass = NULL;
! 
!       /* override the NULLs with the user params if passed */
!       if (my_opts->remotehost)
        {
!               pghost = (char *) malloc(128);
!               sscanf(my_opts->_hostname, "%s", pghost);
        }
  
!       if (my_opts->remoteport)
        {
!               pgport = (char *) malloc(6);
!               sscanf(my_opts->_port, "%s", pgport);
        }
! 
!       if (my_opts->remoteuser)
        {
!               pguser = (char *) malloc(128);
!               sscanf(my_opts->_username, "%s", pguser);
        }
  
!       if (my_opts->remotepass)
        {
!               pgpass = (char *) malloc(128);
!               sscanf(my_opts->_password, "%s", pgpass);
        }
  
        /* login */
!       conn = PQsetdbLogin(pghost, pgport, pgoptions, pgtty, dbName, pguser, pgpass);
  
        /* deal with errors */
!       if (PQstatus(conn) == CONNECTION_BAD)
        {
!               fprintf(stderr, "Connection to database '%s' failed.\n", dbName);
                fprintf(stderr, "%s", PQerrorMessage(conn));
  
                PQfinish(conn);
                exit(1);
        }
  
-       /* free data structures: not strictly necessary */
-       if (pghost != NULL)
-               free(pghost);
-       if (pgport != NULL)
-               free(pgport);
-       if (pguser != NULL)
-               free(pguser);
-       if (pgpass != NULL)
-               free(pgpass);
- 
-       sql_exec(conn, "SET search_path = public;", 0);
- 
        /* return the conn if good */
        return conn;
  }
  
! /* If the sql_ command has an error,  this function looks up the error number and 
prints it out. */
! void
! sql_exec_error(int error_number)
! {
!       fprintf(stderr, "Error number %i.\n", error_number);
!       switch (error_number)
!       {
!               case 3:
!                       fprintf(stderr, "Error:  PGRES_COPY_OUT\n");
!                       break;
! 
!               case 4:
!                       fprintf(stderr, "Error:  PGRES_COPY_IN\n");
!                       break;
! 
!               case 5:
!                       fprintf(stderr, "Error:  PGRES_BAD_RESPONCE\n");
!                       break;
! 
!               case 6:
!                       fprintf(stderr, "Error:  PGRES_NONFATAL_ERROR\n");
!                       break;
! 
!               case 7:
!                       fprintf(stderr, "Error:  PGRES_FATAL_ERROR\n");
!                       break;
!       }
! }
! 
! /* actual code to make call to the database and print the output data */
  int
! sql_exec(PGconn *conn, const char *todo, int match)
  {
        PGresult   *res;
  
!       int                     numbfields;
!       int                     error_number;
!       int                     i,
!                               len;
  
        /* make the call */
        res = PQexec(conn, todo);
--- 62,298 ----
        int                     c;
  
        /* set the defaults */
!       my_opts->quiet = false;
!       my_opts->systables = false;
!       my_opts->indexes = false;
!       my_opts->nodb = false;
!       my_opts->extended = false;
!       my_opts->tablespaces = false;
  
        /* get opts */
!       while ((c = getopt(argc, argv, "H:p:U:P:d:t:o:f:qSxish?")) != -1)
        {
                switch (c)
                {
                                /* specify the database */
                        case 'd':
!                               my_opts->dbname = (char *) myalloc(strlen(optarg));
!                               sscanf(optarg, "%s", my_opts->dbname);
                                break;
  
!                               /* specify one tablename to show */
                        case 't':
!                               add_one_elt(optarg, my_opts->tables);
                                break;
  
!                               /* specify one Oid to show */
                        case 'o':
!                               add_one_elt(optarg, my_opts->oids);
!                               break;
  
+                               /* specify one filenode to show*/
+                       case 'f':
+                               add_one_elt(optarg, my_opts->filenodes);
                                break;
  
+                               /* don't show headers */
                        case 'q':
!                               my_opts->quiet = true;
                                break;
  
                                /* host to connect to */
                        case 'H':
!                               my_opts->hostname = (char *) myalloc(strlen(optarg));
!                               sscanf(optarg, "%s", my_opts->hostname);
                                break;
  
                                /* port to connect to on remote host */
                        case 'p':
!                               my_opts->port = (char *) myalloc(strlen(optarg));
!                               sscanf(optarg, "%s", my_opts->port);
                                break;
  
                                /* username */
                        case 'U':
!                               my_opts->username = (char *) myalloc(strlen(optarg));
!                               sscanf(optarg, "%s", my_opts->username);
                                break;
  
                                /* password */
                        case 'P':
!                               my_opts->password = (char *) myalloc(strlen(optarg));
!                               sscanf(optarg, "%s", my_opts->password);
                                break;
  
                                /* display system tables */
+                       case 'S':
+                               my_opts->systables = true;
+                               break;
+ 
+                               /* also display indexes */
+                       case 'i':
+                               my_opts->indexes = true;
+                               break;
+ 
+                               /* display extra columns */
                        case 'x':
!                               my_opts->extended = true;
!                               break;
! 
!                               /* dump tablespaces only */
!                       case 's':
!                               my_opts->tablespaces = true;
                                break;
  
                                /* help! (ugly in code for easier editing) */
                        case '?':
                        case 'h':
!                               fprintf(stderr,
! "Usage: oid2name [-s|-d database] [-S][-i][-q][-x] [-t table|-o oid|-f file] ...\n"
! "        default action        show all database Oids\n"
! "        -d database           database to connect to\n"
! "        -s                    show all tablespaces\n"
! "        -S                    show system objects too\n"
! "        -i                    show indexes and sequences too\n"
! "        -x                    extended (show additional columns)\n"
! "        -q                    quiet (don't show headers)\n"
! "        -t <table>            show Oid of <table>\n"
! "        -o <oid>              show table name of <oid>\n"
! "        -f <filename>         show table name of a filename\n"
! "        -H host               connect to remote host\n"
! "        -p port               host port to connect to\n"
! "        -U username           username to connect with\n"
! "        -P password           password for username\n"
! "                              (also see $PGPASSWORD and ~/.pgpass)\n"
! );
                                exit(1);
                                break;
                }
        }
  }
  
! void *
! myalloc(size_t size)
  {
!       void *ptr = malloc(size);
!       if (!ptr)
        {
!               fprintf(stderr, "out of memory");
!               exit(1);
        }
+       return ptr;
+ }
  
! /*
!  * add_one_elt
!  *
!  * Add one element to a (possibly empty) eary struct.
!  */
! void
! add_one_elt(char *eltname, eary *eary)
! {
!       if (eary->alloc == 0)
        {
!               eary->alloc = 8;
!               eary->array = (char **) myalloc(8 * sizeof(char *));
        }
!       else if (eary->num >= eary->alloc)
        {
!               eary->alloc *= 2;
!               eary->array = (char **)
!                       realloc(eary->array, eary->alloc * sizeof(char *));
!               if (!eary->array)
!               {
!                       fprintf(stderr, "out of memory");
!                       exit(1);
!               }
        }
  
!       eary->array[eary->num] = strdup(eltname);
!       eary->num++;
! }
! 
! /*
!  * get_comma_elts
!  *
!  * Return the elements of an eary as a (freshly allocated) single string, in
!  * single quotes, separated by commas and properly escaped for insertion in an
!  * SQL statement.
!  */
! char *
! get_comma_elts(eary *eary)
! {
!       char *ret,
!                *ptr;
!       int i, length = 0;
! 
!       if (eary->num == 0)
!               return "";
! 
!       /*
!        * PQescapeString wants 2 * length + 1 bytes of breath space.  Add two
!        * chars per element for the single quotes and one for the comma.
!        */
!       for (i = 0; i < eary->num; i++)
!               length += strlen(eary->array[i]);
! 
!       ret = (char *) myalloc(length * 2 + 4 * eary->num);
!       ptr = ret;
! 
!       for (i = 0; i < eary->num; i++)
        {
!               if (i != 0)
!                       sprintf(ptr++, ",");
!               sprintf(ptr++, "'");
!               ptr += PQescapeString(ptr, eary->array[i], strlen(eary->array[i]));
!               sprintf(ptr++, "'");
        }
  
+       return ret;
+ }
+ 
+ /* establish connection with database. */
+ PGconn *
+ sql_conn(struct options * my_opts)
+ {
+       PGconn     *conn;
+ 
        /* login */
!       conn = PQsetdbLogin(my_opts->hostname,
!                                   my_opts->port,
!                                               NULL,  /* options */
!                                               NULL,  /* tty */
!                                               my_opts->dbname,
!                                               my_opts->username,
!                                               my_opts->password);
  
        /* deal with errors */
!       if (PQstatus(conn) != CONNECTION_OK)
        {
!               fprintf(stderr, "%s: connection to database '%s' failed.\n", 
"oid2name", my_opts->dbname);
                fprintf(stderr, "%s", PQerrorMessage(conn));
  
                PQfinish(conn);
                exit(1);
        }
  
        /* return the conn if good */
        return conn;
  }
  
! /*
!  * Actual code to make call to the database and print the output data.
!  */
  int
! sql_exec(PGconn *conn, const char *todo, bool quiet)
  {
        PGresult   *res;
  
!       int                     nfields;
!       int                     nrows;
!       int                     i, j, l;
!       int                *length;
!       char       *pad;
  
        /* make the call */
        res = PQexec(conn, todo);
***************
*** 297,306 ****
        /* check and deal with errors */
        if (!res || PQresultStatus(res) > 2)
        {
!               error_number = PQresultStatus(res);
!               fprintf(stderr, "There was an error in the SQL command:\n%s\n", todo);
!               sql_exec_error(error_number);
!               fprintf(stderr, "PQerrorMessage = %s\n", PQerrorMessage(conn));
  
                PQclear(res);
                PQfinish(conn);
--- 300,307 ----
        /* check and deal with errors */
        if (!res || PQresultStatus(res) > 2)
        {
!               fprintf(stderr, "oid2name: query failed: %s\n", PQerrorMessage(conn));
!               fprintf(stderr, "oid2name: query was: %s\n", todo);
  
                PQclear(res);
                PQfinish(conn);
***************
*** 308,404 ****
        }
  
        /* get the number of fields */
!       numbfields = PQntuples(res);
  
!       /* if we only expect 1 and there mode than,  return -2 */
!       if (match == 1 && numbfields > 1)
!               return -2;
  
!       /* return -1 if there aren't any returns */
!       if (match == 1 && numbfields < 1)
!               return -1;
  
!       /* for each row,  dump the information */
!       for (i = 0; i < numbfields; i++)
        {
!               len = strlen(PQgetvalue(res, i, 0));
  
!               fprintf(stdout, "%-6s = %s\n", PQgetvalue(res, i, 0), PQgetvalue(res, 
i, 1));
        }
  
!       /* clean the PGconn once done */
        PQclear(res);
  
        return 0;
  }
  
! /* dump all databases known by the system table */
  void
! sql_exec_dumpdb(PGconn *conn)
  {
        char            todo[1024];
  
        /* get the oid and database name from the system pg_database table */
!       snprintf(todo, 1024, "select oid,datname from pg_database");
  
!       sql_exec(conn, todo, 0);
  }
  
! /* display all tables in whatever db we are connected to.  don't display the
!    system tables by default */
  void
! sql_exec_dumptable(PGconn *conn, int systables)
  {
        char            todo[1024];
  
!       /* don't exclude the systables if this is set */
!       if (systables == 1)
!               snprintf(todo, 1024, "select relfilenode,relname from pg_class order 
by relname");
!       else
!               snprintf(todo, 1024, "select relfilenode,relname from pg_class "
!                                "where relkind not in ('v','s', 'c') and "
!                                "relname not like 'pg_%%' order by relname");
  
!       sql_exec(conn, todo, 0);
  }
  
! /* display the oid for a given tablename for whatever db we are connected
!    to.        do we want to allow %bar% in the search?  Not now. */
  void
! sql_exec_searchtable(PGconn *conn, const char *tablename)
  {
!       int                     returnvalue;
!       char            todo[1024];
! 
!       /* get the oid and tablename where the name matches tablename */
!       snprintf(todo, 1024, "select relfilenode,relname from pg_class where relname = 
'%s'", tablename);
  
!       returnvalue = sql_exec(conn, todo, 1);
  
!       /* deal with the return errors */
!       if (returnvalue == -1)
!               printf("No tables with that name found\n");
! 
!       if (returnvalue == -2)
!               printf("VERY scary:  more than one table with that name found!!\n");
  }
  
- /* same as above */
  void
! sql_exec_searchoid(PGconn *conn, int oid)
  {
!       int                     returnvalue;
!       char            todo[1024];
! 
!       snprintf(todo, 1024, "select relfilenode,relname from pg_class where oid = 
%i", oid);
  
!       returnvalue = sql_exec(conn, todo, 1);
  
!       if (returnvalue == -1)
!               printf("No tables with that oid found\n");
! 
!       if (returnvalue == -2)
!               printf("VERY scary:  more than one table with that oid found!!\n");
  }
  
  int
--- 309,487 ----
        }
  
        /* get the number of fields */
!       nrows = PQntuples(res);
!       nfields = PQnfields(res);
  
!       /* for each field, get the needed width */
!       length = (int *) myalloc(sizeof(int) * nfields);
!       for (j = 0; j < nfields; j++)
!               length[j] = strlen(PQfname(res, j));
  
!       for (i = 0; i < nrows; i++)
!       {
!               for (j = 0; j < nfields; j++)
!               {
!                       l = strlen(PQgetvalue(res, i, j));
!                       if (l > length[j])
!                               length[j] = strlen(PQgetvalue(res, i, j));
!               }
!       }
  
!       /* print a header */
!       if (!quiet)
        {
!               for (j = 0, l = 0; j < nfields; j++)
!               {
!                       fprintf(stdout, "%*s", length[j] + 2, PQfname(res, j));
!                       l += length[j] + 2;
!               }
!               fprintf(stdout, "\n");
!               pad = (char *) myalloc(l + 1);
!               MemSet(pad, '-', l);
!               pad[l] = '\0';
!               fprintf(stdout, "%s\n", pad);
!               free(pad);
!       }
  
!       /* for each row, dump the information */
!       for (i = 0; i < nrows; i++)
!       {
!               for (j = 0; j < nfields; j++)
!                       fprintf(stdout, "%*s", length[j] + 2, PQgetvalue(res, i, j));
!               fprintf(stdout, "\n");
        }
  
!       /* cleanup */
        PQclear(res);
+       free(length);
  
        return 0;
  }
  
! /*
!  * Dump all databases.  There are no system objects to worry about.
!  */
  void
! sql_exec_dumpalldbs(PGconn *conn, struct options *opts)
  {
        char            todo[1024];
  
        /* get the oid and database name from the system pg_database table */
!       snprintf(todo, 1024, "SELECT d.oid AS \"Oid\", datname AS \"Database Name\", "
!                        "spcname AS \"Tablespace\" FROM pg_database d JOIN 
pg_tablespace t ON "
!                        "(dattablespace = t.oid) ORDER BY 2");
  
!       sql_exec(conn, todo, opts->quiet);
  }
  
! /* 
!  * Dump all tables, indexes and sequences in the current database.
!  */
  void
! sql_exec_dumpalltables(PGconn *conn, struct options *opts)
  {
        char            todo[1024];
+       char       *addfields = ",c.oid AS \"Oid\", nspname AS \"Schema\", spcname as 
\"Tablespace\" ";
  
!       snprintf(todo, 1024,
!                        "SELECT relfilenode as \"Filenode\", relname as \"Table 
Name\" %s "
!                        "FROM pg_class c "
!                        "      LEFT JOIN pg_catalog.pg_namespace n ON n.oid = 
c.relnamespace "
!                        "      LEFT JOIN pg_catalog.pg_database d ON d.datname = 
current_database(),"
!                        "      pg_catalog.pg_tablespace t "
!                        "WHERE relkind IN ('r'%s) AND "
!                        "      %s"
!                        "              t.oid = CASE"
!                        "                      WHEN reltablespace <> 0 THEN 
reltablespace"
!                        "                      WHEN n.nsptablespace <> 0 THEN 
nsptablespace"
!                        "                      WHEN d.dattablespace <> 0 THEN 
dattablespace"
!                        "              END "
!                        "ORDER BY relname",
!                        opts->extended ? addfields : "",
!                        opts->indexes ? ", 'i', 'S', 't'" : "",
!                        opts->systables ? "" : "n.nspname NOT IN ('pg_catalog', 
'pg_toast', 'information_schema') AND");
  
!       sql_exec(conn, todo, opts->quiet);
  }
  
! /*
!  * Show oid, relfilenode, name, schema and tablespace for each of the
!  * given objects in the current database.
!  */
  void
! sql_exec_searchtables(PGconn *conn, struct options *opts)
  {
!       char       *todo;
!       char       *qualifiers, *ptr;
!       char       *comma_oids, *comma_filenodes, *comma_tables;
!       bool            written = false;
!       char       *addfields = ",c.oid AS \"Oid\", nspname AS \"Schema\", spcname as 
\"Tablespace\" ";
! 
!       /* get tables qualifiers, whether names, relfilenodes, or OIDs */
!       comma_oids = get_comma_elts(opts->oids);
!       comma_tables = get_comma_elts(opts->tables);
!       comma_filenodes = get_comma_elts(opts->filenodes);
! 
!       /* 80 extra chars for SQL expression */
!       qualifiers = (char *) myalloc(strlen(comma_oids) + strlen(comma_tables) +
!                                                                 
strlen(comma_filenodes) + 80);
!       ptr = qualifiers;
! 
!       if (opts->oids->num > 0)
!       {
!               ptr += sprintf(ptr, "c.oid IN (%s)", comma_oids);
!               written = true;
!       }
!       if (opts->filenodes->num > 0)
!       {
!               if (written)
!                       ptr += sprintf(ptr, " OR ");
!               ptr += sprintf(ptr, "c.relfilenode IN (%s)", comma_filenodes);
!               written = true;
!       }
!       if (opts->tables->num > 0)
!       {
!               if (written)
!                       ptr += sprintf(ptr, " OR ");
!               sprintf(ptr, "c.relname ~~ ANY (ARRAY[%s])", comma_tables);
!       }
!       free(comma_oids);
!       free(comma_tables);
!       free(comma_filenodes);
! 
!       /* now build the query */
!       todo = (char *) myalloc(650 + strlen(qualifiers));
!       snprintf(todo, 1024,
!                        "SELECT relfilenode as \"Filenode\", relname as \"Table 
Name\" %s\n"
!                        "FROM pg_class c \n"
!                        "      LEFT JOIN pg_catalog.pg_namespace n ON n.oid = 
c.relnamespace \n"
!                        "      LEFT JOIN pg_catalog.pg_database d ON d.datname = 
current_database(),\n"
!                        "      pg_catalog.pg_tablespace t \n"
!                        "WHERE relkind IN ('r', 'i', 'S', 't') AND \n"
!                        "              t.oid = CASE\n"
!                        "                      WHEN reltablespace <> 0 THEN 
reltablespace\n"
!                        "                      WHEN n.nsptablespace <> 0 THEN 
nsptablespace\n"
!                        "                      WHEN d.dattablespace <> 0 THEN 
dattablespace\n"
!                        "              END AND \n"
!                        "  (%s) \n"
!                        "ORDER BY relname\n",
!                        opts->extended ? addfields : "",
!                        qualifiers);
  
!       free(qualifiers);
  
!       sql_exec(conn, todo, opts->quiet);
  }
  
  void
! sql_exec_dumpalltbspc(PGconn *conn, struct options *opts)
  {
!       char    todo[1024];
  
!       snprintf(todo, 1024, "SELECT oid AS \"Oid\", spcname as \"Tablespace Name\"\n"
!                        "FROM pg_tablespace");
  
!       sql_exec(conn, todo, opts->quiet);
  }
  
  int
***************
*** 407,471 ****
        struct options *my_opts;
        PGconn     *pgconn;
  
!       my_opts = (struct options *) malloc(sizeof(struct options));
  
        /* parse the opts */
        get_opts(argc, argv, my_opts);
  
!       /* display all the tables in the database */
!       if (my_opts->getdatabase & my_opts->gettable)
        {
                if (!my_opts->quiet)
!               {
!                       printf("Oid of table %s from database \"%s\":\n", 
my_opts->_tbname, my_opts->_dbname);
!                       printf("---------------------------------\n");
!               }
!               pgconn = sql_conn(my_opts->_dbname, my_opts);
!               sql_exec_searchtable(pgconn, my_opts->_tbname);
!               PQfinish(pgconn);
  
!               exit(1);
        }
  
!       /* search for the tablename of the given OID */
!       if (my_opts->getdatabase & my_opts->getoid)
        {
                if (!my_opts->quiet)
!               {
!                       printf("Tablename of oid %i from database \"%s\":\n", 
my_opts->_oid, my_opts->_dbname);
!                       printf("---------------------------------\n");
!               }
!               pgconn = sql_conn(my_opts->_dbname, my_opts);
!               sql_exec_searchoid(pgconn, my_opts->_oid);
!               PQfinish(pgconn);
  
!               exit(1);
        }
  
!       /* search for the oid for the given tablename */
!       if (my_opts->getdatabase)
        {
                if (!my_opts->quiet)
!               {
!                       printf("All tables from database \"%s\":\n", my_opts->_dbname);
!                       printf("---------------------------------\n");
!               }
!               pgconn = sql_conn(my_opts->_dbname, my_opts);
!               sql_exec_dumptable(pgconn, my_opts->systables);
!               PQfinish(pgconn);
  
!               exit(1);
        }
  
!       /* display all the databases for the server we are connected to.. */
        if (!my_opts->quiet)
-       {
                printf("All databases:\n");
!               printf("---------------------------------\n");
!       }
!       pgconn = sql_conn("template1", my_opts);
!       sql_exec_dumpdb(pgconn);
!       PQfinish(pgconn);
  
        exit(0);
  }
--- 490,555 ----
        struct options *my_opts;
        PGconn     *pgconn;
  
!       my_opts = (struct options *) myalloc(sizeof(struct options));
! 
!       my_opts->oids = (eary *) myalloc(sizeof(eary));
!       my_opts->tables = (eary *) myalloc(sizeof(eary));
!       my_opts->filenodes = (eary *) myalloc(sizeof(eary));
! 
!       my_opts->oids->num = my_opts->oids->alloc = 0;
!       my_opts->tables->num = my_opts->tables->alloc = 0;
!       my_opts->filenodes->num = my_opts->filenodes->alloc = 0;
  
        /* parse the opts */
        get_opts(argc, argv, my_opts);
  
!       if (my_opts->dbname == NULL)
!       {
!               my_opts->dbname = "template1";
!               my_opts->nodb = true;
!       }
!       pgconn = sql_conn(my_opts);
! 
!       /* display only tablespaces */
!       if (my_opts->tablespaces)
        {
                if (!my_opts->quiet)
!                       printf("All tablespaces:\n");
!               sql_exec_dumpalltbspc(pgconn, my_opts);
  
!               PQfinish(pgconn);
!               exit(0);
        }
  
!       /* display the given elements in the database */
!       if (my_opts->oids->num > 0 ||
!               my_opts->tables->num > 0 ||
!               my_opts->filenodes->num > 0)
        {
                if (!my_opts->quiet)
!                       printf("From database \"%s\":\n", my_opts->dbname);
!               sql_exec_searchtables(pgconn, my_opts);
  
!               PQfinish(pgconn);
!               exit(0);
        }
  
!       /* no elements given; dump the given database */
!       if (my_opts->dbname && !my_opts->nodb)
        {
                if (!my_opts->quiet)
!                       printf("From database \"%s\":\n", my_opts->dbname);
!               sql_exec_dumpalltables(pgconn, my_opts);
  
!               PQfinish(pgconn);
!               exit(0);
        }
  
!       /* no database either; dump all databases */
        if (!my_opts->quiet)
                printf("All databases:\n");
!       sql_exec_dumpalldbs(pgconn, my_opts);
  
+       PQfinish(pgconn);
        exit(0);
  }
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Reply via email to