Thread: Make pg_dump dump conversions

Make pg_dump dump conversions

From

Christopher Kings-Lynne

Date:

20 October 2003, 03:46:46

Save this for 7.5.  Nails a TODO item.

Chris

? GNUmakefile
? config.log
? config.status
? src/Makefile.global
? src/backend/postgres
? src/backend/access/common/.deps
? src/backend/access/gist/.deps
? src/backend/access/hash/.deps
? src/backend/access/heap/.deps
? src/backend/access/index/.deps
? src/backend/access/nbtree/.deps
? src/backend/access/rtree/.deps
? src/backend/access/transam/.deps
? src/backend/bootstrap/.deps
? src/backend/catalog/.deps
? src/backend/catalog/postgres.bki
? src/backend/catalog/postgres.description
? src/backend/commands/.deps
? src/backend/executor/.deps
? src/backend/lib/.deps
? src/backend/libpq/.deps
? src/backend/main/.deps
? src/backend/nodes/.deps
? src/backend/optimizer/geqo/.deps
? src/backend/optimizer/path/.deps
? src/backend/optimizer/plan/.deps
? src/backend/optimizer/prep/.deps
? src/backend/optimizer/util/.deps
? src/backend/parser/.deps
? src/backend/port/.deps
? src/backend/postmaster/.deps
? src/backend/regex/.deps
? src/backend/rewrite/.deps
? src/backend/storage/buffer/.deps
? src/backend/storage/file/.deps
? src/backend/storage/freespace/.deps
? src/backend/storage/ipc/.deps
? src/backend/storage/large_object/.deps
? src/backend/storage/lmgr/.deps
? src/backend/storage/page/.deps
? src/backend/storage/smgr/.deps
? src/backend/tcop/.deps
? src/backend/utils/.deps
? src/backend/utils/adt/.deps
? src/backend/utils/cache/.deps
? src/backend/utils/error/.deps
? src/backend/utils/fmgr/.deps
? src/backend/utils/hash/.deps
? src/backend/utils/init/.deps
? src/backend/utils/mb/.deps
? src/backend/utils/mb/conversion_procs/conversion_create.sql
? src/backend/utils/mb/conversion_procs/ascii_and_mic/.deps
? src/backend/utils/mb/conversion_procs/ascii_and_mic/libascii_and_mic.so.0
? src/backend/utils/mb/conversion_procs/cyrillic_and_mic/.deps
? src/backend/utils/mb/conversion_procs/cyrillic_and_mic/libcyrillic_and_mic.so.0
? src/backend/utils/mb/conversion_procs/euc_cn_and_mic/.deps
? src/backend/utils/mb/conversion_procs/euc_cn_and_mic/libeuc_cn_and_mic.so.0
? src/backend/utils/mb/conversion_procs/euc_jp_and_sjis/.deps
? src/backend/utils/mb/conversion_procs/euc_jp_and_sjis/libeuc_jp_and_sjis.so.0
? src/backend/utils/mb/conversion_procs/euc_kr_and_mic/.deps
? src/backend/utils/mb/conversion_procs/euc_kr_and_mic/libeuc_kr_and_mic.so.0
? src/backend/utils/mb/conversion_procs/euc_tw_and_big5/.deps
? src/backend/utils/mb/conversion_procs/euc_tw_and_big5/libeuc_tw_and_big5.so.0
? src/backend/utils/mb/conversion_procs/latin2_and_win1250/.deps
? src/backend/utils/mb/conversion_procs/latin2_and_win1250/liblatin2_and_win1250.so.0
? src/backend/utils/mb/conversion_procs/latin_and_mic/.deps
? src/backend/utils/mb/conversion_procs/latin_and_mic/liblatin_and_mic.so.0
? src/backend/utils/mb/conversion_procs/utf8_and_ascii/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_ascii/libutf8_and_ascii.so.0
? src/backend/utils/mb/conversion_procs/utf8_and_big5/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_big5/libutf8_and_big5.so.0
? src/backend/utils/mb/conversion_procs/utf8_and_cyrillic/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_cyrillic/libutf8_and_cyrillic.so.0
? src/backend/utils/mb/conversion_procs/utf8_and_euc_cn/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_euc_cn/libutf8_and_euc_cn.so.0
? src/backend/utils/mb/conversion_procs/utf8_and_euc_jp/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_euc_jp/libutf8_and_euc_jp.so.0
? src/backend/utils/mb/conversion_procs/utf8_and_euc_kr/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_euc_kr/libutf8_and_euc_kr.so.0
? src/backend/utils/mb/conversion_procs/utf8_and_euc_tw/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_euc_tw/libutf8_and_euc_tw.so.0
? src/backend/utils/mb/conversion_procs/utf8_and_gb18030/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_gb18030/libutf8_and_gb18030.so.0
? src/backend/utils/mb/conversion_procs/utf8_and_gbk/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_gbk/libutf8_and_gbk.so.0
? src/backend/utils/mb/conversion_procs/utf8_and_iso8859/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_iso8859/libutf8_and_iso8859.so.0
? src/backend/utils/mb/conversion_procs/utf8_and_iso8859_1/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_iso8859_1/libutf8_and_iso8859_1.so.0
? src/backend/utils/mb/conversion_procs/utf8_and_johab/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_johab/libutf8_and_johab.so.0
? src/backend/utils/mb/conversion_procs/utf8_and_sjis/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_sjis/libutf8_and_sjis.so.0
? src/backend/utils/mb/conversion_procs/utf8_and_tcvn/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_tcvn/libutf8_and_tcvn.so.0
? src/backend/utils/mb/conversion_procs/utf8_and_uhc/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_uhc/libutf8_and_uhc.so.0
? src/backend/utils/mb/conversion_procs/utf8_and_win1250/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_win1250/libutf8_and_win1250.so.0
? src/backend/utils/mb/conversion_procs/utf8_and_win1256/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_win1256/libutf8_and_win1256.so.0
? src/backend/utils/mb/conversion_procs/utf8_and_win874/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_win874/libutf8_and_win874.so.0
? src/backend/utils/misc/.deps
? src/backend/utils/mmgr/.deps
? src/backend/utils/sort/.deps
? src/backend/utils/time/.deps
? src/bin/initdb/initdb
? src/bin/initlocation/initlocation
? src/bin/ipcclean/ipcclean
? src/bin/pg_config/pg_config
? src/bin/pg_controldata/.deps
? src/bin/pg_controldata/pg_controldata
? src/bin/pg_ctl/pg_ctl
? src/bin/pg_dump/.deps
? src/bin/pg_dump/pg_dump
? src/bin/pg_dump/pg_dumpall
? src/bin/pg_dump/pg_restore
? src/bin/pg_encoding/.deps
? src/bin/pg_encoding/pg_encoding
? src/bin/pg_id/.deps
? src/bin/pg_id/pg_id
? src/bin/pg_resetxlog/.deps
? src/bin/pg_resetxlog/pg_resetxlog
? src/bin/psql/.deps
? src/bin/psql/psql
? src/bin/scripts/.deps
? src/bin/scripts/clusterdb
? src/bin/scripts/createdb
? src/bin/scripts/createlang
? src/bin/scripts/createuser
? src/bin/scripts/dropdb
? src/bin/scripts/droplang
? src/bin/scripts/dropuser
? src/bin/scripts/vacuumdb
? src/include/pg_config.h
? src/include/stamp-h
? src/interfaces/ecpg/compatlib/.deps
? src/interfaces/ecpg/compatlib/libecpg_compat.so.1
? src/interfaces/ecpg/ecpglib/.deps
? src/interfaces/ecpg/ecpglib/libecpg.so.4
? src/interfaces/ecpg/pgtypeslib/.deps
? src/interfaces/ecpg/pgtypeslib/libpgtypes.so.1
? src/interfaces/ecpg/preproc/.deps
? src/interfaces/ecpg/preproc/ecpg
? src/interfaces/libpq/.deps
? src/interfaces/libpq/libpq.so.3
? src/pl/plperl/.deps
? src/pl/plperl/SPI.c
? src/pl/plperl/libplperl.so.0
? src/pl/plpgsql/src/.deps
? src/pl/plpgsql/src/libplpgsql.so.1
? src/port/.deps
Index: src/bin/pg_dump/common.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/bin/pg_dump/common.c,v
retrieving revision 1.75
diff -c -r1.75 common.c
*** src/bin/pg_dump/common.c    4 Aug 2003 02:40:09 -0000    1.75
--- src/bin/pg_dump/common.c    20 Oct 2003 03:41:34 -0000
***************
*** 61,66 ****
--- 61,67 ----
      int            numAggregates;
      int            numOperators;
      int            numOpclasses;
+     int            numConversions;
      NamespaceInfo *nsinfo;
      TypeInfo   *tinfo;
      FuncInfo   *finfo;
***************
*** 69,74 ****
--- 70,76 ----
      InhInfo    *inhinfo;
      OprInfo    *oprinfo;
      OpclassInfo *opcinfo;
+     ConvInfo *convinfo;

      if (g_verbose)
          write_msg(NULL, "reading schemas\n");
***************
*** 95,100 ****
--- 97,106 ----
      opcinfo = getOpclasses(&numOpclasses);

      if (g_verbose)
+         write_msg(NULL, "reading user-defined conversions\n");
+     convinfo = getConversions(&numConversions);
+
+     if (g_verbose)
          write_msg(NULL, "reading user-defined tables\n");
      tblinfo = getTables(&numTables);

***************
*** 188,193 ****
--- 194,206 ----
          if (g_verbose)
              write_msg(NULL, "dumping out user-defined casts\n");
          dumpCasts(fout, finfo, numFuncs, tinfo, numTypes);
+     }
+
+     if (!dataOnly)
+     {
+         if (g_verbose)
+             write_msg(NULL, "dumping out user-defined conversions\n");
+         dumpConversions(fout, convinfo, numConversions);
      }

      *numTablesPtr = numTables;
Index: src/bin/pg_dump/pg_dump.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/bin/pg_dump/pg_dump.c,v
retrieving revision 1.353
diff -c -r1.353 pg_dump.c
*** src/bin/pg_dump/pg_dump.c    8 Oct 2003 03:52:32 -0000    1.353
--- src/bin/pg_dump/pg_dump.c    20 Oct 2003 03:41:36 -0000
***************
*** 105,110 ****
--- 105,111 ----
  static const char *convertOperatorReference(const char *opr,
                           OprInfo *g_oprinfo, int numOperators);
  static void dumpOneOpclass(Archive *fout, OpclassInfo *opcinfo);
+ static void dumpOneConversion(Archive *fout, ConvInfo *convinfo);
  static void dumpOneAgg(Archive *fout, AggInfo *agginfo);
  static Oid    findLastBuiltinOid_V71(const char *);
  static Oid    findLastBuiltinOid_V70(void);
***************
*** 1690,1695 ****
--- 1691,1763 ----
  }

  /*
+  * getConversions:
+  *      read all conversions in the system catalogs and return them in the
+  * ConvInfo* structure
+  *
+  *    numConversions is set to the number of conversions read in
+  */
+ ConvInfo *
+ getConversions(int *numConversions)
+ {
+     PGresult   *res;
+     int            ntups;
+     int            i;
+     PQExpBuffer query = createPQExpBuffer();
+     ConvInfo *convinfo;
+     int            i_oid;
+     int            i_conname;
+     int            i_connamespace;
+     int            i_usename;
+
+     /*
+      * find all conversions, including builtin conversions; we filter out
+      * system-defined conversions at dump-out time.
+      */
+
+     /* Make sure we are in proper schema */
+     selectSourceSchema("pg_catalog");
+
+     appendPQExpBuffer(query, "SELECT pg_conversion.oid, conname, "
+                       "connamespace, "
+                       "(select usename from pg_user where conowner = usesysid) as usename "
+                       "from pg_conversion");
+
+     res = PQexec(g_conn, query->data);
+     if (!res ||
+         PQresultStatus(res) != PGRES_TUPLES_OK)
+     {
+         write_msg(NULL, "query to obtain list of conversions failed: %s", PQerrorMessage(g_conn));
+         exit_nicely();
+     }
+
+     ntups = PQntuples(res);
+     *numConversions = ntups;
+
+     convinfo = (ConvInfo *) malloc(ntups * sizeof(ConvInfo));
+
+     i_oid = PQfnumber(res, "oid");
+     i_conname = PQfnumber(res, "conname");
+     i_connamespace = PQfnumber(res, "connamespace");
+     i_usename = PQfnumber(res, "usename");
+
+     for (i = 0; i < ntups; i++)
+     {
+         convinfo[i].oid = strdup(PQgetvalue(res, i, i_oid));
+         convinfo[i].conname = strdup(PQgetvalue(res, i, i_conname));
+         convinfo[i].connamespace = findNamespace(PQgetvalue(res, i, i_connamespace),
+                                                 convinfo[i].oid);
+         convinfo[i].usename = strdup(PQgetvalue(res, i, i_usename));
+     }
+
+     PQclear(res);
+
+     destroyPQExpBuffer(query);
+
+     return convinfo;
+ }
+
+ /*
   * getOpclasses:
   *      read all opclasses in the system catalogs and return them in the
   * OpclassInfo* structure
***************
*** 4624,4629 ****
--- 4692,4826 ----
      destroyPQExpBuffer(delq);
  }

+ /*
+  * dumpConversions
+  *      writes out to fout the queries to create all the user-defined conversions
+  */
+ void
+ dumpConversions(Archive *fout, ConvInfo convinfo[], int numConvs)
+ {
+     int            i;
+
+     for (i = 0; i < numConvs; i++)
+     {
+         /* Dump only conversions in dumpable namespaces */
+         if (!convinfo[i].connamespace->dump)
+             continue;
+
+         dumpOneConversion(fout, &convinfo[i]);
+     }
+ }
+
+ /*
+  * dumpOneConversion
+  *      write out a single conversion definition
+  */
+ static void
+ dumpOneConversion(Archive *fout, ConvInfo *convinfo)
+ {
+     PQExpBuffer query = createPQExpBuffer();
+     PQExpBuffer q = createPQExpBuffer();
+     PQExpBuffer delq = createPQExpBuffer();
+     PQExpBuffer details = createPQExpBuffer();
+     PGresult   *res;
+     int            ntups;
+     int            i_conname;
+     int            i_conforencoding;
+     int            i_contoencoding;
+     int            i_conproc;
+     int            i_condefault;
+     const char *conname;
+     const char *conforencoding;
+     const char *contoencoding;
+     const char *conproc;
+     bool        condefault;
+
+     /* Make sure we are in proper schema */
+     selectSourceSchema(convinfo->connamespace->nspname);
+
+     /* Get conversion-specific details */
+     if (g_fout->remoteVersion >= 70300)
+     {
+         appendPQExpBuffer(query, "SELECT conname,
+                         pg_catalog.pg_encoding_to_char(conforencoding) AS conforencoding,
+                         pg_catalog.pg_encoding_to_char(contoencoding) AS contoencoding,
+                         conproc, condefault
+                     FROM pg_catalog.pg_conversion c
+                     WHERE c.oid = '%s'::pg_catalog.oid",
+                         convinfo->oid);
+     }
+     /* XXX: what do I do here? */
+     else return;
+
+     res = PQexec(g_conn, query->data);
+     if (!res ||
+         PQresultStatus(res) != PGRES_TUPLES_OK)
+     {
+         write_msg(NULL, "query to obtain conversion failed: %s",
+                   PQerrorMessage(g_conn));
+         exit_nicely();
+     }
+
+     /* Expecting a single result only */
+     ntups = PQntuples(res);
+     if (ntups != 1)
+     {
+         write_msg(NULL, "Got %d rows instead of one from: %s",
+                   ntups, query->data);
+         exit_nicely();
+     }
+
+     i_conname = PQfnumber(res, "conname");
+     i_conforencoding = PQfnumber(res, "conforencoding");
+     i_contoencoding = PQfnumber(res, "contoencoding");
+     i_conproc = PQfnumber(res, "conproc");
+     i_condefault = PQfnumber(res, "condefault");
+
+     conname = PQgetvalue(res, 0, i_conname);
+     conforencoding = PQgetvalue(res, 0, i_conforencoding);
+     contoencoding = PQgetvalue(res, 0, i_contoencoding);
+     conproc = PQgetvalue(res, 0, i_conproc);
+     condefault = (PQgetvalue(res, 0, i_condefault)[0] == 't');
+
+     /*
+      * DROP must be fully qualified in case same name appears in
+      * pg_catalog
+      */
+     appendPQExpBuffer(delq, "DROP CONVERSION %s",
+                       fmtId(convinfo->connamespace->nspname));
+     appendPQExpBuffer(delq, ".%s;\n",
+                       fmtId(convinfo->conname));
+
+     appendPQExpBuffer(q, "CREATE %sCONVERSION %s FOR ",
+                     (condefault) ? "DEFAULT " : "",
+                     fmtId(convinfo->conname));
+     appendStringLiteral(q, conforencoding, true);
+     appendPQExpBuffer(q, " TO ");
+     appendStringLiteral(q, contoencoding, true);
+     /* regproc is automatically quoted in 7.3 and above */
+     appendPQExpBuffer(q, " FROM %s;\n", conproc);
+
+     ArchiveEntry(fout, convinfo->oid, convinfo->conname,
+                  convinfo->connamespace->nspname, convinfo->usename,
+                  "CONVERSION", NULL,
+                  q->data, delq->data,
+                  NULL, NULL, NULL);
+
+     /* Dump Conversion Comments */
+
+     resetPQExpBuffer(q);
+     appendPQExpBuffer(q, "CONVERSION %s", convinfo->conname);
+     dumpComment(fout, q->data,
+                 convinfo->connamespace->nspname, convinfo->usename,
+                 convinfo->oid, "pg_conversion", 0, NULL);
+
+     PQclear(res);
+
+     destroyPQExpBuffer(query);
+     destroyPQExpBuffer(q);
+     destroyPQExpBuffer(delq);
+     destroyPQExpBuffer(details);
+ }

  /*
   * dumpAggs
Index: src/bin/pg_dump/pg_dump.h
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/bin/pg_dump/pg_dump.h,v
retrieving revision 1.104
diff -c -r1.104 pg_dump.h
*** src/bin/pg_dump/pg_dump.h    8 Aug 2003 04:52:21 -0000    1.104
--- src/bin/pg_dump/pg_dump.h    20 Oct 2003 03:41:36 -0000
***************
*** 95,100 ****
--- 95,108 ----
      char       *usename;
  } OpclassInfo;

+ typedef struct _convInfo
+ {
+     char       *oid;
+     char       *conname;
+     NamespaceInfo *connamespace;    /* link to containing namespace */
+     char       *usename;
+ } ConvInfo;
+
  typedef struct _tableInfo
  {
      /*
***************
*** 213,218 ****
--- 221,227 ----
  extern AggInfo *getAggregates(int *numAggregates);
  extern OprInfo *getOperators(int *numOperators);
  extern OpclassInfo *getOpclasses(int *numOpclasses);
+ extern ConvInfo *getConversions(int *numConversions);
  extern TableInfo *getTables(int *numTables);
  extern InhInfo *getInherits(int *numInherits);

***************
*** 230,235 ****
--- 239,246 ----
  extern void dumpOprs(Archive *fout, OprInfo *oprinfo, int numOperators);
  extern void dumpOpclasses(Archive *fout,
                OpclassInfo *opcinfo, int numOpclasses);
+ extern void dumpConversions(Archive *fout,
+               ConvInfo *coninfo, int numConversions);
  extern void dumpTables(Archive *fout, TableInfo tblinfo[], int numTables,
             const bool aclsSkip,
             const bool schemaOnly, const bool dataOnly);

Re: Make pg_dump dump conversions

From

Christopher Kings-Lynne

Date:

20 October 2003, 07:10:45

Just noticed a bug in the previous version of the patch.  Would fail
against pre-7.3 postgres.

Fixed in this new version.  Just discard the old.

Chris


Christopher Kings-Lynne wrote:

> Save this for 7.5.  Nails a TODO item.
>
> Chris

? GNUmakefile
? config.log
? config.status
? src/Makefile.global
? src/backend/postgres
? src/backend/access/common/.deps
? src/backend/access/gist/.deps
? src/backend/access/hash/.deps
? src/backend/access/heap/.deps
? src/backend/access/index/.deps
? src/backend/access/nbtree/.deps
? src/backend/access/rtree/.deps
? src/backend/access/transam/.deps
? src/backend/bootstrap/.deps
? src/backend/catalog/.deps
? src/backend/catalog/postgres.bki
? src/backend/catalog/postgres.description
? src/backend/commands/.deps
? src/backend/executor/.deps
? src/backend/lib/.deps
? src/backend/libpq/.deps
? src/backend/main/.deps
? src/backend/nodes/.deps
? src/backend/optimizer/geqo/.deps
? src/backend/optimizer/path/.deps
? src/backend/optimizer/plan/.deps
? src/backend/optimizer/prep/.deps
? src/backend/optimizer/util/.deps
? src/backend/parser/.deps
? src/backend/port/.deps
? src/backend/postmaster/.deps
? src/backend/regex/.deps
? src/backend/rewrite/.deps
? src/backend/storage/buffer/.deps
? src/backend/storage/file/.deps
? src/backend/storage/freespace/.deps
? src/backend/storage/ipc/.deps
? src/backend/storage/large_object/.deps
? src/backend/storage/lmgr/.deps
? src/backend/storage/page/.deps
? src/backend/storage/smgr/.deps
? src/backend/tcop/.deps
? src/backend/utils/.deps
? src/backend/utils/adt/.deps
? src/backend/utils/cache/.deps
? src/backend/utils/error/.deps
? src/backend/utils/fmgr/.deps
? src/backend/utils/hash/.deps
? src/backend/utils/init/.deps
? src/backend/utils/mb/.deps
? src/backend/utils/mb/conversion_procs/conversion_create.sql
? src/backend/utils/mb/conversion_procs/ascii_and_mic/.deps
? src/backend/utils/mb/conversion_procs/ascii_and_mic/libascii_and_mic.so.0
? src/backend/utils/mb/conversion_procs/cyrillic_and_mic/.deps
? src/backend/utils/mb/conversion_procs/cyrillic_and_mic/libcyrillic_and_mic.so.0
? src/backend/utils/mb/conversion_procs/euc_cn_and_mic/.deps
? src/backend/utils/mb/conversion_procs/euc_cn_and_mic/libeuc_cn_and_mic.so.0
? src/backend/utils/mb/conversion_procs/euc_jp_and_sjis/.deps
? src/backend/utils/mb/conversion_procs/euc_jp_and_sjis/libeuc_jp_and_sjis.so.0
? src/backend/utils/mb/conversion_procs/euc_kr_and_mic/.deps
? src/backend/utils/mb/conversion_procs/euc_kr_and_mic/libeuc_kr_and_mic.so.0
? src/backend/utils/mb/conversion_procs/euc_tw_and_big5/.deps
? src/backend/utils/mb/conversion_procs/euc_tw_and_big5/libeuc_tw_and_big5.so.0
? src/backend/utils/mb/conversion_procs/latin2_and_win1250/.deps
? src/backend/utils/mb/conversion_procs/latin2_and_win1250/liblatin2_and_win1250.so.0
? src/backend/utils/mb/conversion_procs/latin_and_mic/.deps
? src/backend/utils/mb/conversion_procs/latin_and_mic/liblatin_and_mic.so.0
? src/backend/utils/mb/conversion_procs/utf8_and_ascii/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_ascii/libutf8_and_ascii.so.0
? src/backend/utils/mb/conversion_procs/utf8_and_big5/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_big5/libutf8_and_big5.so.0
? src/backend/utils/mb/conversion_procs/utf8_and_cyrillic/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_cyrillic/libutf8_and_cyrillic.so.0
? src/backend/utils/mb/conversion_procs/utf8_and_euc_cn/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_euc_cn/libutf8_and_euc_cn.so.0
? src/backend/utils/mb/conversion_procs/utf8_and_euc_jp/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_euc_jp/libutf8_and_euc_jp.so.0
? src/backend/utils/mb/conversion_procs/utf8_and_euc_kr/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_euc_kr/libutf8_and_euc_kr.so.0
? src/backend/utils/mb/conversion_procs/utf8_and_euc_tw/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_euc_tw/libutf8_and_euc_tw.so.0
? src/backend/utils/mb/conversion_procs/utf8_and_gb18030/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_gb18030/libutf8_and_gb18030.so.0
? src/backend/utils/mb/conversion_procs/utf8_and_gbk/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_gbk/libutf8_and_gbk.so.0
? src/backend/utils/mb/conversion_procs/utf8_and_iso8859/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_iso8859/libutf8_and_iso8859.so.0
? src/backend/utils/mb/conversion_procs/utf8_and_iso8859_1/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_iso8859_1/libutf8_and_iso8859_1.so.0
? src/backend/utils/mb/conversion_procs/utf8_and_johab/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_johab/libutf8_and_johab.so.0
? src/backend/utils/mb/conversion_procs/utf8_and_sjis/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_sjis/libutf8_and_sjis.so.0
? src/backend/utils/mb/conversion_procs/utf8_and_tcvn/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_tcvn/libutf8_and_tcvn.so.0
? src/backend/utils/mb/conversion_procs/utf8_and_uhc/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_uhc/libutf8_and_uhc.so.0
? src/backend/utils/mb/conversion_procs/utf8_and_win1250/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_win1250/libutf8_and_win1250.so.0
? src/backend/utils/mb/conversion_procs/utf8_and_win1256/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_win1256/libutf8_and_win1256.so.0
? src/backend/utils/mb/conversion_procs/utf8_and_win874/.deps
? src/backend/utils/mb/conversion_procs/utf8_and_win874/libutf8_and_win874.so.0
? src/backend/utils/misc/.deps
? src/backend/utils/mmgr/.deps
? src/backend/utils/sort/.deps
? src/backend/utils/time/.deps
? src/bin/initdb/initdb
? src/bin/initlocation/initlocation
? src/bin/ipcclean/ipcclean
? src/bin/pg_config/pg_config
? src/bin/pg_controldata/.deps
? src/bin/pg_controldata/pg_controldata
? src/bin/pg_ctl/pg_ctl
? src/bin/pg_dump/.deps
? src/bin/pg_dump/pg_dump
? src/bin/pg_dump/pg_dump.core
? src/bin/pg_dump/pg_dumpall
? src/bin/pg_dump/pg_restore
? src/bin/pg_encoding/.deps
? src/bin/pg_encoding/pg_encoding
? src/bin/pg_id/.deps
? src/bin/pg_id/pg_id
? src/bin/pg_resetxlog/.deps
? src/bin/pg_resetxlog/pg_resetxlog
? src/bin/psql/.deps
? src/bin/psql/psql
? src/bin/scripts/.deps
? src/bin/scripts/clusterdb
? src/bin/scripts/createdb
? src/bin/scripts/createlang
? src/bin/scripts/createuser
? src/bin/scripts/dropdb
? src/bin/scripts/droplang
? src/bin/scripts/dropuser
? src/bin/scripts/vacuumdb
? src/include/pg_config.h
? src/include/stamp-h
? src/interfaces/ecpg/compatlib/.deps
? src/interfaces/ecpg/compatlib/libecpg_compat.so.1
? src/interfaces/ecpg/ecpglib/.deps
? src/interfaces/ecpg/ecpglib/libecpg.so.4
? src/interfaces/ecpg/pgtypeslib/.deps
? src/interfaces/ecpg/pgtypeslib/libpgtypes.so.1
? src/interfaces/ecpg/preproc/.deps
? src/interfaces/ecpg/preproc/ecpg
? src/interfaces/libpq/.deps
? src/interfaces/libpq/libpq.so.3
? src/pl/plperl/.deps
? src/pl/plperl/SPI.c
? src/pl/plperl/libplperl.so.0
? src/pl/plpgsql/src/.deps
? src/pl/plpgsql/src/libplpgsql.so.1
? src/port/.deps
Index: src/bin/pg_dump/common.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/bin/pg_dump/common.c,v
retrieving revision 1.75
diff -c -r1.75 common.c
*** src/bin/pg_dump/common.c    4 Aug 2003 02:40:09 -0000    1.75
--- src/bin/pg_dump/common.c    20 Oct 2003 07:01:11 -0000
***************
*** 61,66 ****
--- 61,67 ----
      int            numAggregates;
      int            numOperators;
      int            numOpclasses;
+     int            numConversions;
      NamespaceInfo *nsinfo;
      TypeInfo   *tinfo;
      FuncInfo   *finfo;
***************
*** 69,74 ****
--- 70,76 ----
      InhInfo    *inhinfo;
      OprInfo    *oprinfo;
      OpclassInfo *opcinfo;
+     ConvInfo *convinfo;

      if (g_verbose)
          write_msg(NULL, "reading schemas\n");
***************
*** 95,100 ****
--- 97,106 ----
      opcinfo = getOpclasses(&numOpclasses);

      if (g_verbose)
+         write_msg(NULL, "reading user-defined conversions\n");
+     convinfo = getConversions(&numConversions);
+
+     if (g_verbose)
          write_msg(NULL, "reading user-defined tables\n");
      tblinfo = getTables(&numTables);

***************
*** 188,193 ****
--- 194,206 ----
          if (g_verbose)
              write_msg(NULL, "dumping out user-defined casts\n");
          dumpCasts(fout, finfo, numFuncs, tinfo, numTypes);
+     }
+
+     if (!dataOnly)
+     {
+         if (g_verbose)
+             write_msg(NULL, "dumping out user-defined conversions\n");
+         dumpConversions(fout, convinfo, numConversions);
      }

      *numTablesPtr = numTables;
Index: src/bin/pg_dump/pg_dump.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/bin/pg_dump/pg_dump.c,v
retrieving revision 1.353
diff -c -r1.353 pg_dump.c
*** src/bin/pg_dump/pg_dump.c    8 Oct 2003 03:52:32 -0000    1.353
--- src/bin/pg_dump/pg_dump.c    20 Oct 2003 07:01:13 -0000
***************
*** 105,110 ****
--- 105,111 ----
  static const char *convertOperatorReference(const char *opr,
                           OprInfo *g_oprinfo, int numOperators);
  static void dumpOneOpclass(Archive *fout, OpclassInfo *opcinfo);
+ static void dumpOneConversion(Archive *fout, ConvInfo *convinfo);
  static void dumpOneAgg(Archive *fout, AggInfo *agginfo);
  static Oid    findLastBuiltinOid_V71(const char *);
  static Oid    findLastBuiltinOid_V70(void);
***************
*** 1690,1695 ****
--- 1691,1769 ----
  }

  /*
+  * getConversions:
+  *      read all conversions in the system catalogs and return them in the
+  * ConvInfo* structure
+  *
+  *    numConversions is set to the number of conversions read in
+  */
+ ConvInfo *
+ getConversions(int *numConversions)
+ {
+     PGresult   *res;
+     int            ntups;
+     int            i;
+     PQExpBuffer query = createPQExpBuffer();
+     ConvInfo *convinfo;
+     int            i_oid;
+     int            i_conname;
+     int            i_connamespace;
+     int            i_usename;
+
+     /* Conversions didn't exist pre-7.3 */
+     if (g_fout->remoteVersion < 70300) {
+         *numConversions = 0;
+         return NULL;
+     }
+
+     /*
+      * find all conversions, including builtin conversions; we filter out
+      * system-defined conversions at dump-out time.
+      */
+
+     /* Make sure we are in proper schema */
+     selectSourceSchema("pg_catalog");
+
+     appendPQExpBuffer(query, "SELECT pg_conversion.oid, conname, "
+                       "connamespace, "
+                       "(select usename from pg_user where conowner = usesysid) as usename "
+                       "from pg_conversion");
+
+     res = PQexec(g_conn, query->data);
+     if (!res ||
+         PQresultStatus(res) != PGRES_TUPLES_OK)
+     {
+         write_msg(NULL, "query to obtain list of conversions failed: %s", PQerrorMessage(g_conn));
+         exit_nicely();
+     }
+
+     ntups = PQntuples(res);
+     *numConversions = ntups;
+
+     convinfo = (ConvInfo *) malloc(ntups * sizeof(ConvInfo));
+
+     i_oid = PQfnumber(res, "oid");
+     i_conname = PQfnumber(res, "conname");
+     i_connamespace = PQfnumber(res, "connamespace");
+     i_usename = PQfnumber(res, "usename");
+
+     for (i = 0; i < ntups; i++)
+     {
+         convinfo[i].oid = strdup(PQgetvalue(res, i, i_oid));
+         convinfo[i].conname = strdup(PQgetvalue(res, i, i_conname));
+         convinfo[i].connamespace = findNamespace(PQgetvalue(res, i, i_connamespace),
+                                                 convinfo[i].oid);
+         convinfo[i].usename = strdup(PQgetvalue(res, i, i_usename));
+     }
+
+     PQclear(res);
+
+     destroyPQExpBuffer(query);
+
+     return convinfo;
+ }
+
+ /*
   * getOpclasses:
   *      read all opclasses in the system catalogs and return them in the
   * OpclassInfo* structure
***************
*** 4624,4629 ****
--- 4698,4827 ----
      destroyPQExpBuffer(delq);
  }

+ /*
+  * dumpConversions
+  *      writes out to fout the queries to create all the user-defined conversions
+  */
+ void
+ dumpConversions(Archive *fout, ConvInfo convinfo[], int numConvs)
+ {
+     int            i;
+
+     for (i = 0; i < numConvs; i++)
+     {
+         /* Dump only conversions in dumpable namespaces */
+         if (!convinfo[i].connamespace->dump)
+             continue;
+
+         dumpOneConversion(fout, &convinfo[i]);
+     }
+ }
+
+ /*
+  * dumpOneConversion
+  *      write out a single conversion definition
+  */
+ static void
+ dumpOneConversion(Archive *fout, ConvInfo *convinfo)
+ {
+     PQExpBuffer query = createPQExpBuffer();
+     PQExpBuffer q = createPQExpBuffer();
+     PQExpBuffer delq = createPQExpBuffer();
+     PQExpBuffer details = createPQExpBuffer();
+     PGresult   *res;
+     int            ntups;
+     int            i_conname;
+     int            i_conforencoding;
+     int            i_contoencoding;
+     int            i_conproc;
+     int            i_condefault;
+     const char *conname;
+     const char *conforencoding;
+     const char *contoencoding;
+     const char *conproc;
+     bool        condefault;
+
+     /* Make sure we are in proper schema */
+     selectSourceSchema(convinfo->connamespace->nspname);
+
+     /* Get conversion-specific details */
+     appendPQExpBuffer(query, "SELECT conname,
+                     pg_catalog.pg_encoding_to_char(conforencoding) AS conforencoding,
+                     pg_catalog.pg_encoding_to_char(contoencoding) AS contoencoding,
+                     conproc, condefault
+                 FROM pg_catalog.pg_conversion c
+                 WHERE c.oid = '%s'::pg_catalog.oid",
+                     convinfo->oid);
+
+     res = PQexec(g_conn, query->data);
+     if (!res ||
+         PQresultStatus(res) != PGRES_TUPLES_OK)
+     {
+         write_msg(NULL, "query to obtain conversion failed: %s",
+                   PQerrorMessage(g_conn));
+         exit_nicely();
+     }
+
+     /* Expecting a single result only */
+     ntups = PQntuples(res);
+     if (ntups != 1)
+     {
+         write_msg(NULL, "Got %d rows instead of one from: %s",
+                   ntups, query->data);
+         exit_nicely();
+     }
+
+     i_conname = PQfnumber(res, "conname");
+     i_conforencoding = PQfnumber(res, "conforencoding");
+     i_contoencoding = PQfnumber(res, "contoencoding");
+     i_conproc = PQfnumber(res, "conproc");
+     i_condefault = PQfnumber(res, "condefault");
+
+     conname = PQgetvalue(res, 0, i_conname);
+     conforencoding = PQgetvalue(res, 0, i_conforencoding);
+     contoencoding = PQgetvalue(res, 0, i_contoencoding);
+     conproc = PQgetvalue(res, 0, i_conproc);
+     condefault = (PQgetvalue(res, 0, i_condefault)[0] == 't');
+
+     /*
+      * DROP must be fully qualified in case same name appears in
+      * pg_catalog
+      */
+     appendPQExpBuffer(delq, "DROP CONVERSION %s",
+                       fmtId(convinfo->connamespace->nspname));
+     appendPQExpBuffer(delq, ".%s;\n",
+                       fmtId(convinfo->conname));
+
+     appendPQExpBuffer(q, "CREATE %sCONVERSION %s FOR ",
+                     (condefault) ? "DEFAULT " : "",
+                     fmtId(convinfo->conname));
+     appendStringLiteral(q, conforencoding, true);
+     appendPQExpBuffer(q, " TO ");
+     appendStringLiteral(q, contoencoding, true);
+     /* regproc is automatically quoted in 7.3 and above */
+     appendPQExpBuffer(q, " FROM %s;\n", conproc);
+
+     ArchiveEntry(fout, convinfo->oid, convinfo->conname,
+                  convinfo->connamespace->nspname, convinfo->usename,
+                  "CONVERSION", NULL,
+                  q->data, delq->data,
+                  NULL, NULL, NULL);
+
+     /* Dump Conversion Comments */
+
+     resetPQExpBuffer(q);
+     appendPQExpBuffer(q, "CONVERSION %s", convinfo->conname);
+     dumpComment(fout, q->data,
+                 convinfo->connamespace->nspname, convinfo->usename,
+                 convinfo->oid, "pg_conversion", 0, NULL);
+
+     PQclear(res);
+
+     destroyPQExpBuffer(query);
+     destroyPQExpBuffer(q);
+     destroyPQExpBuffer(delq);
+     destroyPQExpBuffer(details);
+ }

  /*
   * dumpAggs
Index: src/bin/pg_dump/pg_dump.h
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/bin/pg_dump/pg_dump.h,v
retrieving revision 1.104
diff -c -r1.104 pg_dump.h
*** src/bin/pg_dump/pg_dump.h    8 Aug 2003 04:52:21 -0000    1.104
--- src/bin/pg_dump/pg_dump.h    20 Oct 2003 07:01:13 -0000
***************
*** 95,100 ****
--- 95,108 ----
      char       *usename;
  } OpclassInfo;

+ typedef struct _convInfo
+ {
+     char       *oid;
+     char       *conname;
+     NamespaceInfo *connamespace;    /* link to containing namespace */
+     char       *usename;
+ } ConvInfo;
+
  typedef struct _tableInfo
  {
      /*
***************
*** 213,218 ****
--- 221,227 ----
  extern AggInfo *getAggregates(int *numAggregates);
  extern OprInfo *getOperators(int *numOperators);
  extern OpclassInfo *getOpclasses(int *numOpclasses);
+ extern ConvInfo *getConversions(int *numConversions);
  extern TableInfo *getTables(int *numTables);
  extern InhInfo *getInherits(int *numInherits);

***************
*** 230,235 ****
--- 239,246 ----
  extern void dumpOprs(Archive *fout, OprInfo *oprinfo, int numOperators);
  extern void dumpOpclasses(Archive *fout,
                OpclassInfo *opcinfo, int numOpclasses);
+ extern void dumpConversions(Archive *fout,
+               ConvInfo *coninfo, int numConversions);
  extern void dumpTables(Archive *fout, TableInfo tblinfo[], int numTables,
             const bool aclsSkip,
             const bool schemaOnly, const bool dataOnly);

Re: Make pg_dump dump conversions

From

Bruce Momjian

Date:

21 October 2003, 02:50:36

This has been saved for the 7.5 release:

    http:/momjian.postgresql.org/cgi-bin/pgpatches2

---------------------------------------------------------------------------

Christopher Kings-Lynne wrote:
> Just noticed a bug in the previous version of the patch.  Would fail
> against pre-7.3 postgres.
>
> Fixed in this new version.  Just discard the old.
>
> Chris
>
>
> Christopher Kings-Lynne wrote:
>
> > Save this for 7.5.  Nails a TODO item.
> >
> > Chris
>

> ? GNUmakefile
> ? config.log
> ? config.status
> ? src/Makefile.global
> ? src/backend/postgres
> ? src/backend/access/common/.deps
> ? src/backend/access/gist/.deps
> ? src/backend/access/hash/.deps
> ? src/backend/access/heap/.deps
> ? src/backend/access/index/.deps
> ? src/backend/access/nbtree/.deps
> ? src/backend/access/rtree/.deps
> ? src/backend/access/transam/.deps
> ? src/backend/bootstrap/.deps
> ? src/backend/catalog/.deps
> ? src/backend/catalog/postgres.bki
> ? src/backend/catalog/postgres.description
> ? src/backend/commands/.deps
> ? src/backend/executor/.deps
> ? src/backend/lib/.deps
> ? src/backend/libpq/.deps
> ? src/backend/main/.deps
> ? src/backend/nodes/.deps
> ? src/backend/optimizer/geqo/.deps
> ? src/backend/optimizer/path/.deps
> ? src/backend/optimizer/plan/.deps
> ? src/backend/optimizer/prep/.deps
> ? src/backend/optimizer/util/.deps
> ? src/backend/parser/.deps
> ? src/backend/port/.deps
> ? src/backend/postmaster/.deps
> ? src/backend/regex/.deps
> ? src/backend/rewrite/.deps
> ? src/backend/storage/buffer/.deps
> ? src/backend/storage/file/.deps
> ? src/backend/storage/freespace/.deps
> ? src/backend/storage/ipc/.deps
> ? src/backend/storage/large_object/.deps
> ? src/backend/storage/lmgr/.deps
> ? src/backend/storage/page/.deps
> ? src/backend/storage/smgr/.deps
> ? src/backend/tcop/.deps
> ? src/backend/utils/.deps
> ? src/backend/utils/adt/.deps
> ? src/backend/utils/cache/.deps
> ? src/backend/utils/error/.deps
> ? src/backend/utils/fmgr/.deps
> ? src/backend/utils/hash/.deps
> ? src/backend/utils/init/.deps
> ? src/backend/utils/mb/.deps
> ? src/backend/utils/mb/conversion_procs/conversion_create.sql
> ? src/backend/utils/mb/conversion_procs/ascii_and_mic/.deps
> ? src/backend/utils/mb/conversion_procs/ascii_and_mic/libascii_and_mic.so.0
> ? src/backend/utils/mb/conversion_procs/cyrillic_and_mic/.deps
> ? src/backend/utils/mb/conversion_procs/cyrillic_and_mic/libcyrillic_and_mic.so.0
> ? src/backend/utils/mb/conversion_procs/euc_cn_and_mic/.deps
> ? src/backend/utils/mb/conversion_procs/euc_cn_and_mic/libeuc_cn_and_mic.so.0
> ? src/backend/utils/mb/conversion_procs/euc_jp_and_sjis/.deps
> ? src/backend/utils/mb/conversion_procs/euc_jp_and_sjis/libeuc_jp_and_sjis.so.0
> ? src/backend/utils/mb/conversion_procs/euc_kr_and_mic/.deps
> ? src/backend/utils/mb/conversion_procs/euc_kr_and_mic/libeuc_kr_and_mic.so.0
> ? src/backend/utils/mb/conversion_procs/euc_tw_and_big5/.deps
> ? src/backend/utils/mb/conversion_procs/euc_tw_and_big5/libeuc_tw_and_big5.so.0
> ? src/backend/utils/mb/conversion_procs/latin2_and_win1250/.deps
> ? src/backend/utils/mb/conversion_procs/latin2_and_win1250/liblatin2_and_win1250.so.0
> ? src/backend/utils/mb/conversion_procs/latin_and_mic/.deps
> ? src/backend/utils/mb/conversion_procs/latin_and_mic/liblatin_and_mic.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_ascii/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_ascii/libutf8_and_ascii.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_big5/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_big5/libutf8_and_big5.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_cyrillic/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_cyrillic/libutf8_and_cyrillic.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_euc_cn/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_euc_cn/libutf8_and_euc_cn.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_euc_jp/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_euc_jp/libutf8_and_euc_jp.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_euc_kr/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_euc_kr/libutf8_and_euc_kr.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_euc_tw/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_euc_tw/libutf8_and_euc_tw.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_gb18030/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_gb18030/libutf8_and_gb18030.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_gbk/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_gbk/libutf8_and_gbk.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_iso8859/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_iso8859/libutf8_and_iso8859.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_iso8859_1/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_iso8859_1/libutf8_and_iso8859_1.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_johab/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_johab/libutf8_and_johab.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_sjis/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_sjis/libutf8_and_sjis.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_tcvn/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_tcvn/libutf8_and_tcvn.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_uhc/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_uhc/libutf8_and_uhc.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_win1250/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_win1250/libutf8_and_win1250.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_win1256/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_win1256/libutf8_and_win1256.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_win874/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_win874/libutf8_and_win874.so.0
> ? src/backend/utils/misc/.deps
> ? src/backend/utils/mmgr/.deps
> ? src/backend/utils/sort/.deps
> ? src/backend/utils/time/.deps
> ? src/bin/initdb/initdb
> ? src/bin/initlocation/initlocation
> ? src/bin/ipcclean/ipcclean
> ? src/bin/pg_config/pg_config
> ? src/bin/pg_controldata/.deps
> ? src/bin/pg_controldata/pg_controldata
> ? src/bin/pg_ctl/pg_ctl
> ? src/bin/pg_dump/.deps
> ? src/bin/pg_dump/pg_dump
> ? src/bin/pg_dump/pg_dump.core
> ? src/bin/pg_dump/pg_dumpall
> ? src/bin/pg_dump/pg_restore
> ? src/bin/pg_encoding/.deps
> ? src/bin/pg_encoding/pg_encoding
> ? src/bin/pg_id/.deps
> ? src/bin/pg_id/pg_id
> ? src/bin/pg_resetxlog/.deps
> ? src/bin/pg_resetxlog/pg_resetxlog
> ? src/bin/psql/.deps
> ? src/bin/psql/psql
> ? src/bin/scripts/.deps
> ? src/bin/scripts/clusterdb
> ? src/bin/scripts/createdb
> ? src/bin/scripts/createlang
> ? src/bin/scripts/createuser
> ? src/bin/scripts/dropdb
> ? src/bin/scripts/droplang
> ? src/bin/scripts/dropuser
> ? src/bin/scripts/vacuumdb
> ? src/include/pg_config.h
> ? src/include/stamp-h
> ? src/interfaces/ecpg/compatlib/.deps
> ? src/interfaces/ecpg/compatlib/libecpg_compat.so.1
> ? src/interfaces/ecpg/ecpglib/.deps
> ? src/interfaces/ecpg/ecpglib/libecpg.so.4
> ? src/interfaces/ecpg/pgtypeslib/.deps
> ? src/interfaces/ecpg/pgtypeslib/libpgtypes.so.1
> ? src/interfaces/ecpg/preproc/.deps
> ? src/interfaces/ecpg/preproc/ecpg
> ? src/interfaces/libpq/.deps
> ? src/interfaces/libpq/libpq.so.3
> ? src/pl/plperl/.deps
> ? src/pl/plperl/SPI.c
> ? src/pl/plperl/libplperl.so.0
> ? src/pl/plpgsql/src/.deps
> ? src/pl/plpgsql/src/libplpgsql.so.1
> ? src/port/.deps
> Index: src/bin/pg_dump/common.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/bin/pg_dump/common.c,v
> retrieving revision 1.75
> diff -c -r1.75 common.c
> *** src/bin/pg_dump/common.c    4 Aug 2003 02:40:09 -0000    1.75
> --- src/bin/pg_dump/common.c    20 Oct 2003 07:01:11 -0000
> ***************
> *** 61,66 ****
> --- 61,67 ----
>       int            numAggregates;
>       int            numOperators;
>       int            numOpclasses;
> +     int            numConversions;
>       NamespaceInfo *nsinfo;
>       TypeInfo   *tinfo;
>       FuncInfo   *finfo;
> ***************
> *** 69,74 ****
> --- 70,76 ----
>       InhInfo    *inhinfo;
>       OprInfo    *oprinfo;
>       OpclassInfo *opcinfo;
> +     ConvInfo *convinfo;
>
>       if (g_verbose)
>           write_msg(NULL, "reading schemas\n");
> ***************
> *** 95,100 ****
> --- 97,106 ----
>       opcinfo = getOpclasses(&numOpclasses);
>
>       if (g_verbose)
> +         write_msg(NULL, "reading user-defined conversions\n");
> +     convinfo = getConversions(&numConversions);
> +
> +     if (g_verbose)
>           write_msg(NULL, "reading user-defined tables\n");
>       tblinfo = getTables(&numTables);
>
> ***************
> *** 188,193 ****
> --- 194,206 ----
>           if (g_verbose)
>               write_msg(NULL, "dumping out user-defined casts\n");
>           dumpCasts(fout, finfo, numFuncs, tinfo, numTypes);
> +     }
> +
> +     if (!dataOnly)
> +     {
> +         if (g_verbose)
> +             write_msg(NULL, "dumping out user-defined conversions\n");
> +         dumpConversions(fout, convinfo, numConversions);
>       }
>
>       *numTablesPtr = numTables;
> Index: src/bin/pg_dump/pg_dump.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/bin/pg_dump/pg_dump.c,v
> retrieving revision 1.353
> diff -c -r1.353 pg_dump.c
> *** src/bin/pg_dump/pg_dump.c    8 Oct 2003 03:52:32 -0000    1.353
> --- src/bin/pg_dump/pg_dump.c    20 Oct 2003 07:01:13 -0000
> ***************
> *** 105,110 ****
> --- 105,111 ----
>   static const char *convertOperatorReference(const char *opr,
>                            OprInfo *g_oprinfo, int numOperators);
>   static void dumpOneOpclass(Archive *fout, OpclassInfo *opcinfo);
> + static void dumpOneConversion(Archive *fout, ConvInfo *convinfo);
>   static void dumpOneAgg(Archive *fout, AggInfo *agginfo);
>   static Oid    findLastBuiltinOid_V71(const char *);
>   static Oid    findLastBuiltinOid_V70(void);
> ***************
> *** 1690,1695 ****
> --- 1691,1769 ----
>   }
>
>   /*
> +  * getConversions:
> +  *      read all conversions in the system catalogs and return them in the
> +  * ConvInfo* structure
> +  *
> +  *    numConversions is set to the number of conversions read in
> +  */
> + ConvInfo *
> + getConversions(int *numConversions)
> + {
> +     PGresult   *res;
> +     int            ntups;
> +     int            i;
> +     PQExpBuffer query = createPQExpBuffer();
> +     ConvInfo *convinfo;
> +     int            i_oid;
> +     int            i_conname;
> +     int            i_connamespace;
> +     int            i_usename;
> +
> +     /* Conversions didn't exist pre-7.3 */
> +     if (g_fout->remoteVersion < 70300) {
> +         *numConversions = 0;
> +         return NULL;
> +     }
> +
> +     /*
> +      * find all conversions, including builtin conversions; we filter out
> +      * system-defined conversions at dump-out time.
> +      */
> +
> +     /* Make sure we are in proper schema */
> +     selectSourceSchema("pg_catalog");
> +
> +     appendPQExpBuffer(query, "SELECT pg_conversion.oid, conname, "
> +                       "connamespace, "
> +                       "(select usename from pg_user where conowner = usesysid) as usename "
> +                       "from pg_conversion");
> +
> +     res = PQexec(g_conn, query->data);
> +     if (!res ||
> +         PQresultStatus(res) != PGRES_TUPLES_OK)
> +     {
> +         write_msg(NULL, "query to obtain list of conversions failed: %s", PQerrorMessage(g_conn));
> +         exit_nicely();
> +     }
> +
> +     ntups = PQntuples(res);
> +     *numConversions = ntups;
> +
> +     convinfo = (ConvInfo *) malloc(ntups * sizeof(ConvInfo));
> +
> +     i_oid = PQfnumber(res, "oid");
> +     i_conname = PQfnumber(res, "conname");
> +     i_connamespace = PQfnumber(res, "connamespace");
> +     i_usename = PQfnumber(res, "usename");
> +
> +     for (i = 0; i < ntups; i++)
> +     {
> +         convinfo[i].oid = strdup(PQgetvalue(res, i, i_oid));
> +         convinfo[i].conname = strdup(PQgetvalue(res, i, i_conname));
> +         convinfo[i].connamespace = findNamespace(PQgetvalue(res, i, i_connamespace),
> +                                                 convinfo[i].oid);
> +         convinfo[i].usename = strdup(PQgetvalue(res, i, i_usename));
> +     }
> +
> +     PQclear(res);
> +
> +     destroyPQExpBuffer(query);
> +
> +     return convinfo;
> + }
> +
> + /*
>    * getOpclasses:
>    *      read all opclasses in the system catalogs and return them in the
>    * OpclassInfo* structure
> ***************
> *** 4624,4629 ****
> --- 4698,4827 ----
>       destroyPQExpBuffer(delq);
>   }
>
> + /*
> +  * dumpConversions
> +  *      writes out to fout the queries to create all the user-defined conversions
> +  */
> + void
> + dumpConversions(Archive *fout, ConvInfo convinfo[], int numConvs)
> + {
> +     int            i;
> +
> +     for (i = 0; i < numConvs; i++)
> +     {
> +         /* Dump only conversions in dumpable namespaces */
> +         if (!convinfo[i].connamespace->dump)
> +             continue;
> +
> +         dumpOneConversion(fout, &convinfo[i]);
> +     }
> + }
> +
> + /*
> +  * dumpOneConversion
> +  *      write out a single conversion definition
> +  */
> + static void
> + dumpOneConversion(Archive *fout, ConvInfo *convinfo)
> + {
> +     PQExpBuffer query = createPQExpBuffer();
> +     PQExpBuffer q = createPQExpBuffer();
> +     PQExpBuffer delq = createPQExpBuffer();
> +     PQExpBuffer details = createPQExpBuffer();
> +     PGresult   *res;
> +     int            ntups;
> +     int            i_conname;
> +     int            i_conforencoding;
> +     int            i_contoencoding;
> +     int            i_conproc;
> +     int            i_condefault;
> +     const char *conname;
> +     const char *conforencoding;
> +     const char *contoencoding;
> +     const char *conproc;
> +     bool        condefault;
> +
> +     /* Make sure we are in proper schema */
> +     selectSourceSchema(convinfo->connamespace->nspname);
> +
> +     /* Get conversion-specific details */
> +     appendPQExpBuffer(query, "SELECT conname,
> +                     pg_catalog.pg_encoding_to_char(conforencoding) AS conforencoding,
> +                     pg_catalog.pg_encoding_to_char(contoencoding) AS contoencoding,
> +                     conproc, condefault
> +                 FROM pg_catalog.pg_conversion c
> +                 WHERE c.oid = '%s'::pg_catalog.oid",
> +                     convinfo->oid);
> +
> +     res = PQexec(g_conn, query->data);
> +     if (!res ||
> +         PQresultStatus(res) != PGRES_TUPLES_OK)
> +     {
> +         write_msg(NULL, "query to obtain conversion failed: %s",
> +                   PQerrorMessage(g_conn));
> +         exit_nicely();
> +     }
> +
> +     /* Expecting a single result only */
> +     ntups = PQntuples(res);
> +     if (ntups != 1)
> +     {
> +         write_msg(NULL, "Got %d rows instead of one from: %s",
> +                   ntups, query->data);
> +         exit_nicely();
> +     }
> +
> +     i_conname = PQfnumber(res, "conname");
> +     i_conforencoding = PQfnumber(res, "conforencoding");
> +     i_contoencoding = PQfnumber(res, "contoencoding");
> +     i_conproc = PQfnumber(res, "conproc");
> +     i_condefault = PQfnumber(res, "condefault");
> +
> +     conname = PQgetvalue(res, 0, i_conname);
> +     conforencoding = PQgetvalue(res, 0, i_conforencoding);
> +     contoencoding = PQgetvalue(res, 0, i_contoencoding);
> +     conproc = PQgetvalue(res, 0, i_conproc);
> +     condefault = (PQgetvalue(res, 0, i_condefault)[0] == 't');
> +
> +     /*
> +      * DROP must be fully qualified in case same name appears in
> +      * pg_catalog
> +      */
> +     appendPQExpBuffer(delq, "DROP CONVERSION %s",
> +                       fmtId(convinfo->connamespace->nspname));
> +     appendPQExpBuffer(delq, ".%s;\n",
> +                       fmtId(convinfo->conname));
> +
> +     appendPQExpBuffer(q, "CREATE %sCONVERSION %s FOR ",
> +                     (condefault) ? "DEFAULT " : "",
> +                     fmtId(convinfo->conname));
> +     appendStringLiteral(q, conforencoding, true);
> +     appendPQExpBuffer(q, " TO ");
> +     appendStringLiteral(q, contoencoding, true);
> +     /* regproc is automatically quoted in 7.3 and above */
> +     appendPQExpBuffer(q, " FROM %s;\n", conproc);
> +
> +     ArchiveEntry(fout, convinfo->oid, convinfo->conname,
> +                  convinfo->connamespace->nspname, convinfo->usename,
> +                  "CONVERSION", NULL,
> +                  q->data, delq->data,
> +                  NULL, NULL, NULL);
> +
> +     /* Dump Conversion Comments */
> +
> +     resetPQExpBuffer(q);
> +     appendPQExpBuffer(q, "CONVERSION %s", convinfo->conname);
> +     dumpComment(fout, q->data,
> +                 convinfo->connamespace->nspname, convinfo->usename,
> +                 convinfo->oid, "pg_conversion", 0, NULL);
> +
> +     PQclear(res);
> +
> +     destroyPQExpBuffer(query);
> +     destroyPQExpBuffer(q);
> +     destroyPQExpBuffer(delq);
> +     destroyPQExpBuffer(details);
> + }
>
>   /*
>    * dumpAggs
> Index: src/bin/pg_dump/pg_dump.h
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/bin/pg_dump/pg_dump.h,v
> retrieving revision 1.104
> diff -c -r1.104 pg_dump.h
> *** src/bin/pg_dump/pg_dump.h    8 Aug 2003 04:52:21 -0000    1.104
> --- src/bin/pg_dump/pg_dump.h    20 Oct 2003 07:01:13 -0000
> ***************
> *** 95,100 ****
> --- 95,108 ----
>       char       *usename;
>   } OpclassInfo;
>
> + typedef struct _convInfo
> + {
> +     char       *oid;
> +     char       *conname;
> +     NamespaceInfo *connamespace;    /* link to containing namespace */
> +     char       *usename;
> + } ConvInfo;
> +
>   typedef struct _tableInfo
>   {
>       /*
> ***************
> *** 213,218 ****
> --- 221,227 ----
>   extern AggInfo *getAggregates(int *numAggregates);
>   extern OprInfo *getOperators(int *numOperators);
>   extern OpclassInfo *getOpclasses(int *numOpclasses);
> + extern ConvInfo *getConversions(int *numConversions);
>   extern TableInfo *getTables(int *numTables);
>   extern InhInfo *getInherits(int *numInherits);
>
> ***************
> *** 230,235 ****
> --- 239,246 ----
>   extern void dumpOprs(Archive *fout, OprInfo *oprinfo, int numOperators);
>   extern void dumpOpclasses(Archive *fout,
>                 OpclassInfo *opcinfo, int numOpclasses);
> + extern void dumpConversions(Archive *fout,
> +               ConvInfo *coninfo, int numConversions);
>   extern void dumpTables(Archive *fout, TableInfo tblinfo[], int numTables,
>              const bool aclsSkip,
>              const bool schemaOnly, const bool dataOnly);

>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Make pg_dump dump conversions

From

Christopher Kings-Lynne

Date:

19 November 2003, 04:01:42

Hi Bruce,

Don't apply this patch - I'm incorporating it into my COMMENT ON mega
patch.  That way I can get conversion dumping with comments working in
one go.

Chris

Christopher Kings-Lynne wrote:

> Just noticed a bug in the previous version of the patch.  Would fail
> against pre-7.3 postgres.
>
> Fixed in this new version.  Just discard the old.
>
> Chris
>
>
> Christopher Kings-Lynne wrote:
>
>> Save this for 7.5.  Nails a TODO item.
>>
>> Chris
>
>
>
> ------------------------------------------------------------------------
>
> ? GNUmakefile
> ? config.log
> ? config.status
> ? src/Makefile.global
> ? src/backend/postgres
> ? src/backend/access/common/.deps
> ? src/backend/access/gist/.deps
> ? src/backend/access/hash/.deps
> ? src/backend/access/heap/.deps
> ? src/backend/access/index/.deps
> ? src/backend/access/nbtree/.deps
> ? src/backend/access/rtree/.deps
> ? src/backend/access/transam/.deps
> ? src/backend/bootstrap/.deps
> ? src/backend/catalog/.deps
> ? src/backend/catalog/postgres.bki
> ? src/backend/catalog/postgres.description
> ? src/backend/commands/.deps
> ? src/backend/executor/.deps
> ? src/backend/lib/.deps
> ? src/backend/libpq/.deps
> ? src/backend/main/.deps
> ? src/backend/nodes/.deps
> ? src/backend/optimizer/geqo/.deps
> ? src/backend/optimizer/path/.deps
> ? src/backend/optimizer/plan/.deps
> ? src/backend/optimizer/prep/.deps
> ? src/backend/optimizer/util/.deps
> ? src/backend/parser/.deps
> ? src/backend/port/.deps
> ? src/backend/postmaster/.deps
> ? src/backend/regex/.deps
> ? src/backend/rewrite/.deps
> ? src/backend/storage/buffer/.deps
> ? src/backend/storage/file/.deps
> ? src/backend/storage/freespace/.deps
> ? src/backend/storage/ipc/.deps
> ? src/backend/storage/large_object/.deps
> ? src/backend/storage/lmgr/.deps
> ? src/backend/storage/page/.deps
> ? src/backend/storage/smgr/.deps
> ? src/backend/tcop/.deps
> ? src/backend/utils/.deps
> ? src/backend/utils/adt/.deps
> ? src/backend/utils/cache/.deps
> ? src/backend/utils/error/.deps
> ? src/backend/utils/fmgr/.deps
> ? src/backend/utils/hash/.deps
> ? src/backend/utils/init/.deps
> ? src/backend/utils/mb/.deps
> ? src/backend/utils/mb/conversion_procs/conversion_create.sql
> ? src/backend/utils/mb/conversion_procs/ascii_and_mic/.deps
> ? src/backend/utils/mb/conversion_procs/ascii_and_mic/libascii_and_mic.so.0
> ? src/backend/utils/mb/conversion_procs/cyrillic_and_mic/.deps
> ? src/backend/utils/mb/conversion_procs/cyrillic_and_mic/libcyrillic_and_mic.so.0
> ? src/backend/utils/mb/conversion_procs/euc_cn_and_mic/.deps
> ? src/backend/utils/mb/conversion_procs/euc_cn_and_mic/libeuc_cn_and_mic.so.0
> ? src/backend/utils/mb/conversion_procs/euc_jp_and_sjis/.deps
> ? src/backend/utils/mb/conversion_procs/euc_jp_and_sjis/libeuc_jp_and_sjis.so.0
> ? src/backend/utils/mb/conversion_procs/euc_kr_and_mic/.deps
> ? src/backend/utils/mb/conversion_procs/euc_kr_and_mic/libeuc_kr_and_mic.so.0
> ? src/backend/utils/mb/conversion_procs/euc_tw_and_big5/.deps
> ? src/backend/utils/mb/conversion_procs/euc_tw_and_big5/libeuc_tw_and_big5.so.0
> ? src/backend/utils/mb/conversion_procs/latin2_and_win1250/.deps
> ? src/backend/utils/mb/conversion_procs/latin2_and_win1250/liblatin2_and_win1250.so.0
> ? src/backend/utils/mb/conversion_procs/latin_and_mic/.deps
> ? src/backend/utils/mb/conversion_procs/latin_and_mic/liblatin_and_mic.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_ascii/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_ascii/libutf8_and_ascii.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_big5/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_big5/libutf8_and_big5.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_cyrillic/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_cyrillic/libutf8_and_cyrillic.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_euc_cn/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_euc_cn/libutf8_and_euc_cn.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_euc_jp/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_euc_jp/libutf8_and_euc_jp.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_euc_kr/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_euc_kr/libutf8_and_euc_kr.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_euc_tw/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_euc_tw/libutf8_and_euc_tw.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_gb18030/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_gb18030/libutf8_and_gb18030.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_gbk/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_gbk/libutf8_and_gbk.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_iso8859/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_iso8859/libutf8_and_iso8859.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_iso8859_1/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_iso8859_1/libutf8_and_iso8859_1.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_johab/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_johab/libutf8_and_johab.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_sjis/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_sjis/libutf8_and_sjis.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_tcvn/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_tcvn/libutf8_and_tcvn.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_uhc/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_uhc/libutf8_and_uhc.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_win1250/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_win1250/libutf8_and_win1250.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_win1256/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_win1256/libutf8_and_win1256.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_win874/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_win874/libutf8_and_win874.so.0
> ? src/backend/utils/misc/.deps
> ? src/backend/utils/mmgr/.deps
> ? src/backend/utils/sort/.deps
> ? src/backend/utils/time/.deps
> ? src/bin/initdb/initdb
> ? src/bin/initlocation/initlocation
> ? src/bin/ipcclean/ipcclean
> ? src/bin/pg_config/pg_config
> ? src/bin/pg_controldata/.deps
> ? src/bin/pg_controldata/pg_controldata
> ? src/bin/pg_ctl/pg_ctl
> ? src/bin/pg_dump/.deps
> ? src/bin/pg_dump/pg_dump
> ? src/bin/pg_dump/pg_dump.core
> ? src/bin/pg_dump/pg_dumpall
> ? src/bin/pg_dump/pg_restore
> ? src/bin/pg_encoding/.deps
> ? src/bin/pg_encoding/pg_encoding
> ? src/bin/pg_id/.deps
> ? src/bin/pg_id/pg_id
> ? src/bin/pg_resetxlog/.deps
> ? src/bin/pg_resetxlog/pg_resetxlog
> ? src/bin/psql/.deps
> ? src/bin/psql/psql
> ? src/bin/scripts/.deps
> ? src/bin/scripts/clusterdb
> ? src/bin/scripts/createdb
> ? src/bin/scripts/createlang
> ? src/bin/scripts/createuser
> ? src/bin/scripts/dropdb
> ? src/bin/scripts/droplang
> ? src/bin/scripts/dropuser
> ? src/bin/scripts/vacuumdb
> ? src/include/pg_config.h
> ? src/include/stamp-h
> ? src/interfaces/ecpg/compatlib/.deps
> ? src/interfaces/ecpg/compatlib/libecpg_compat.so.1
> ? src/interfaces/ecpg/ecpglib/.deps
> ? src/interfaces/ecpg/ecpglib/libecpg.so.4
> ? src/interfaces/ecpg/pgtypeslib/.deps
> ? src/interfaces/ecpg/pgtypeslib/libpgtypes.so.1
> ? src/interfaces/ecpg/preproc/.deps
> ? src/interfaces/ecpg/preproc/ecpg
> ? src/interfaces/libpq/.deps
> ? src/interfaces/libpq/libpq.so.3
> ? src/pl/plperl/.deps
> ? src/pl/plperl/SPI.c
> ? src/pl/plperl/libplperl.so.0
> ? src/pl/plpgsql/src/.deps
> ? src/pl/plpgsql/src/libplpgsql.so.1
> ? src/port/.deps
> Index: src/bin/pg_dump/common.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/bin/pg_dump/common.c,v
> retrieving revision 1.75
> diff -c -r1.75 common.c
> *** src/bin/pg_dump/common.c    4 Aug 2003 02:40:09 -0000    1.75
> --- src/bin/pg_dump/common.c    20 Oct 2003 07:01:11 -0000
> ***************
> *** 61,66 ****
> --- 61,67 ----
>       int            numAggregates;
>       int            numOperators;
>       int            numOpclasses;
> +     int            numConversions;
>       NamespaceInfo *nsinfo;
>       TypeInfo   *tinfo;
>       FuncInfo   *finfo;
> ***************
> *** 69,74 ****
> --- 70,76 ----
>       InhInfo    *inhinfo;
>       OprInfo    *oprinfo;
>       OpclassInfo *opcinfo;
> +     ConvInfo *convinfo;
>
>       if (g_verbose)
>           write_msg(NULL, "reading schemas\n");
> ***************
> *** 95,100 ****
> --- 97,106 ----
>       opcinfo = getOpclasses(&numOpclasses);
>
>       if (g_verbose)
> +         write_msg(NULL, "reading user-defined conversions\n");
> +     convinfo = getConversions(&numConversions);
> +
> +     if (g_verbose)
>           write_msg(NULL, "reading user-defined tables\n");
>       tblinfo = getTables(&numTables);
>
> ***************
> *** 188,193 ****
> --- 194,206 ----
>           if (g_verbose)
>               write_msg(NULL, "dumping out user-defined casts\n");
>           dumpCasts(fout, finfo, numFuncs, tinfo, numTypes);
> +     }
> +
> +     if (!dataOnly)
> +     {
> +         if (g_verbose)
> +             write_msg(NULL, "dumping out user-defined conversions\n");
> +         dumpConversions(fout, convinfo, numConversions);
>       }
>
>       *numTablesPtr = numTables;
> Index: src/bin/pg_dump/pg_dump.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/bin/pg_dump/pg_dump.c,v
> retrieving revision 1.353
> diff -c -r1.353 pg_dump.c
> *** src/bin/pg_dump/pg_dump.c    8 Oct 2003 03:52:32 -0000    1.353
> --- src/bin/pg_dump/pg_dump.c    20 Oct 2003 07:01:13 -0000
> ***************
> *** 105,110 ****
> --- 105,111 ----
>   static const char *convertOperatorReference(const char *opr,
>                            OprInfo *g_oprinfo, int numOperators);
>   static void dumpOneOpclass(Archive *fout, OpclassInfo *opcinfo);
> + static void dumpOneConversion(Archive *fout, ConvInfo *convinfo);
>   static void dumpOneAgg(Archive *fout, AggInfo *agginfo);
>   static Oid    findLastBuiltinOid_V71(const char *);
>   static Oid    findLastBuiltinOid_V70(void);
> ***************
> *** 1690,1695 ****
> --- 1691,1769 ----
>   }
>
>   /*
> +  * getConversions:
> +  *      read all conversions in the system catalogs and return them in the
> +  * ConvInfo* structure
> +  *
> +  *    numConversions is set to the number of conversions read in
> +  */
> + ConvInfo *
> + getConversions(int *numConversions)
> + {
> +     PGresult   *res;
> +     int            ntups;
> +     int            i;
> +     PQExpBuffer query = createPQExpBuffer();
> +     ConvInfo *convinfo;
> +     int            i_oid;
> +     int            i_conname;
> +     int            i_connamespace;
> +     int            i_usename;
> +
> +     /* Conversions didn't exist pre-7.3 */
> +     if (g_fout->remoteVersion < 70300) {
> +         *numConversions = 0;
> +         return NULL;
> +     }
> +
> +     /*
> +      * find all conversions, including builtin conversions; we filter out
> +      * system-defined conversions at dump-out time.
> +      */
> +
> +     /* Make sure we are in proper schema */
> +     selectSourceSchema("pg_catalog");
> +
> +     appendPQExpBuffer(query, "SELECT pg_conversion.oid, conname, "
> +                       "connamespace, "
> +                       "(select usename from pg_user where conowner = usesysid) as usename "
> +                       "from pg_conversion");
> +
> +     res = PQexec(g_conn, query->data);
> +     if (!res ||
> +         PQresultStatus(res) != PGRES_TUPLES_OK)
> +     {
> +         write_msg(NULL, "query to obtain list of conversions failed: %s", PQerrorMessage(g_conn));
> +         exit_nicely();
> +     }
> +
> +     ntups = PQntuples(res);
> +     *numConversions = ntups;
> +
> +     convinfo = (ConvInfo *) malloc(ntups * sizeof(ConvInfo));
> +
> +     i_oid = PQfnumber(res, "oid");
> +     i_conname = PQfnumber(res, "conname");
> +     i_connamespace = PQfnumber(res, "connamespace");
> +     i_usename = PQfnumber(res, "usename");
> +
> +     for (i = 0; i < ntups; i++)
> +     {
> +         convinfo[i].oid = strdup(PQgetvalue(res, i, i_oid));
> +         convinfo[i].conname = strdup(PQgetvalue(res, i, i_conname));
> +         convinfo[i].connamespace = findNamespace(PQgetvalue(res, i, i_connamespace),
> +                                                 convinfo[i].oid);
> +         convinfo[i].usename = strdup(PQgetvalue(res, i, i_usename));
> +     }
> +
> +     PQclear(res);
> +
> +     destroyPQExpBuffer(query);
> +
> +     return convinfo;
> + }
> +
> + /*
>    * getOpclasses:
>    *      read all opclasses in the system catalogs and return them in the
>    * OpclassInfo* structure
> ***************
> *** 4624,4629 ****
> --- 4698,4827 ----
>       destroyPQExpBuffer(delq);
>   }
>
> + /*
> +  * dumpConversions
> +  *      writes out to fout the queries to create all the user-defined conversions
> +  */
> + void
> + dumpConversions(Archive *fout, ConvInfo convinfo[], int numConvs)
> + {
> +     int            i;
> +
> +     for (i = 0; i < numConvs; i++)
> +     {
> +         /* Dump only conversions in dumpable namespaces */
> +         if (!convinfo[i].connamespace->dump)
> +             continue;
> +
> +         dumpOneConversion(fout, &convinfo[i]);
> +     }
> + }
> +
> + /*
> +  * dumpOneConversion
> +  *      write out a single conversion definition
> +  */
> + static void
> + dumpOneConversion(Archive *fout, ConvInfo *convinfo)
> + {
> +     PQExpBuffer query = createPQExpBuffer();
> +     PQExpBuffer q = createPQExpBuffer();
> +     PQExpBuffer delq = createPQExpBuffer();
> +     PQExpBuffer details = createPQExpBuffer();
> +     PGresult   *res;
> +     int            ntups;
> +     int            i_conname;
> +     int            i_conforencoding;
> +     int            i_contoencoding;
> +     int            i_conproc;
> +     int            i_condefault;
> +     const char *conname;
> +     const char *conforencoding;
> +     const char *contoencoding;
> +     const char *conproc;
> +     bool        condefault;
> +
> +     /* Make sure we are in proper schema */
> +     selectSourceSchema(convinfo->connamespace->nspname);
> +
> +     /* Get conversion-specific details */
> +     appendPQExpBuffer(query, "SELECT conname,
> +                     pg_catalog.pg_encoding_to_char(conforencoding) AS conforencoding,
> +                     pg_catalog.pg_encoding_to_char(contoencoding) AS contoencoding,
> +                     conproc, condefault
> +                 FROM pg_catalog.pg_conversion c
> +                 WHERE c.oid = '%s'::pg_catalog.oid",
> +                     convinfo->oid);
> +
> +     res = PQexec(g_conn, query->data);
> +     if (!res ||
> +         PQresultStatus(res) != PGRES_TUPLES_OK)
> +     {
> +         write_msg(NULL, "query to obtain conversion failed: %s",
> +                   PQerrorMessage(g_conn));
> +         exit_nicely();
> +     }
> +
> +     /* Expecting a single result only */
> +     ntups = PQntuples(res);
> +     if (ntups != 1)
> +     {
> +         write_msg(NULL, "Got %d rows instead of one from: %s",
> +                   ntups, query->data);
> +         exit_nicely();
> +     }
> +
> +     i_conname = PQfnumber(res, "conname");
> +     i_conforencoding = PQfnumber(res, "conforencoding");
> +     i_contoencoding = PQfnumber(res, "contoencoding");
> +     i_conproc = PQfnumber(res, "conproc");
> +     i_condefault = PQfnumber(res, "condefault");
> +
> +     conname = PQgetvalue(res, 0, i_conname);
> +     conforencoding = PQgetvalue(res, 0, i_conforencoding);
> +     contoencoding = PQgetvalue(res, 0, i_contoencoding);
> +     conproc = PQgetvalue(res, 0, i_conproc);
> +     condefault = (PQgetvalue(res, 0, i_condefault)[0] == 't');
> +
> +     /*
> +      * DROP must be fully qualified in case same name appears in
> +      * pg_catalog
> +      */
> +     appendPQExpBuffer(delq, "DROP CONVERSION %s",
> +                       fmtId(convinfo->connamespace->nspname));
> +     appendPQExpBuffer(delq, ".%s;\n",
> +                       fmtId(convinfo->conname));
> +
> +     appendPQExpBuffer(q, "CREATE %sCONVERSION %s FOR ",
> +                     (condefault) ? "DEFAULT " : "",
> +                     fmtId(convinfo->conname));
> +     appendStringLiteral(q, conforencoding, true);
> +     appendPQExpBuffer(q, " TO ");
> +     appendStringLiteral(q, contoencoding, true);
> +     /* regproc is automatically quoted in 7.3 and above */
> +     appendPQExpBuffer(q, " FROM %s;\n", conproc);
> +
> +     ArchiveEntry(fout, convinfo->oid, convinfo->conname,
> +                  convinfo->connamespace->nspname, convinfo->usename,
> +                  "CONVERSION", NULL,
> +                  q->data, delq->data,
> +                  NULL, NULL, NULL);
> +
> +     /* Dump Conversion Comments */
> +
> +     resetPQExpBuffer(q);
> +     appendPQExpBuffer(q, "CONVERSION %s", convinfo->conname);
> +     dumpComment(fout, q->data,
> +                 convinfo->connamespace->nspname, convinfo->usename,
> +                 convinfo->oid, "pg_conversion", 0, NULL);
> +
> +     PQclear(res);
> +
> +     destroyPQExpBuffer(query);
> +     destroyPQExpBuffer(q);
> +     destroyPQExpBuffer(delq);
> +     destroyPQExpBuffer(details);
> + }
>
>   /*
>    * dumpAggs
> Index: src/bin/pg_dump/pg_dump.h
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/bin/pg_dump/pg_dump.h,v
> retrieving revision 1.104
> diff -c -r1.104 pg_dump.h
> *** src/bin/pg_dump/pg_dump.h    8 Aug 2003 04:52:21 -0000    1.104
> --- src/bin/pg_dump/pg_dump.h    20 Oct 2003 07:01:13 -0000
> ***************
> *** 95,100 ****
> --- 95,108 ----
>       char       *usename;
>   } OpclassInfo;
>
> + typedef struct _convInfo
> + {
> +     char       *oid;
> +     char       *conname;
> +     NamespaceInfo *connamespace;    /* link to containing namespace */
> +     char       *usename;
> + } ConvInfo;
> +
>   typedef struct _tableInfo
>   {
>       /*
> ***************
> *** 213,218 ****
> --- 221,227 ----
>   extern AggInfo *getAggregates(int *numAggregates);
>   extern OprInfo *getOperators(int *numOperators);
>   extern OpclassInfo *getOpclasses(int *numOpclasses);
> + extern ConvInfo *getConversions(int *numConversions);
>   extern TableInfo *getTables(int *numTables);
>   extern InhInfo *getInherits(int *numInherits);
>
> ***************
> *** 230,235 ****
> --- 239,246 ----
>   extern void dumpOprs(Archive *fout, OprInfo *oprinfo, int numOperators);
>   extern void dumpOpclasses(Archive *fout,
>                 OpclassInfo *opcinfo, int numOpclasses);
> + extern void dumpConversions(Archive *fout,
> +               ConvInfo *coninfo, int numConversions);
>   extern void dumpTables(Archive *fout, TableInfo tblinfo[], int numTables,
>              const bool aclsSkip,
>              const bool schemaOnly, const bool dataOnly);
>
>
> ------------------------------------------------------------------------
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings

Re: Make pg_dump dump conversions

From

Bruce Momjian

Date:

01 December 2003, 04:01:44

Your patch has been added to the PostgreSQL unapplied patches list at:

    http://momjian.postgresql.org/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.

---------------------------------------------------------------------------


Christopher Kings-Lynne wrote:
> Just noticed a bug in the previous version of the patch.  Would fail
> against pre-7.3 postgres.
>
> Fixed in this new version.  Just discard the old.
>
> Chris
>
>
> Christopher Kings-Lynne wrote:
>
> > Save this for 7.5.  Nails a TODO item.
> >
> > Chris
>

> ? GNUmakefile
> ? config.log
> ? config.status
> ? src/Makefile.global
> ? src/backend/postgres
> ? src/backend/access/common/.deps
> ? src/backend/access/gist/.deps
> ? src/backend/access/hash/.deps
> ? src/backend/access/heap/.deps
> ? src/backend/access/index/.deps
> ? src/backend/access/nbtree/.deps
> ? src/backend/access/rtree/.deps
> ? src/backend/access/transam/.deps
> ? src/backend/bootstrap/.deps
> ? src/backend/catalog/.deps
> ? src/backend/catalog/postgres.bki
> ? src/backend/catalog/postgres.description
> ? src/backend/commands/.deps
> ? src/backend/executor/.deps
> ? src/backend/lib/.deps
> ? src/backend/libpq/.deps
> ? src/backend/main/.deps
> ? src/backend/nodes/.deps
> ? src/backend/optimizer/geqo/.deps
> ? src/backend/optimizer/path/.deps
> ? src/backend/optimizer/plan/.deps
> ? src/backend/optimizer/prep/.deps
> ? src/backend/optimizer/util/.deps
> ? src/backend/parser/.deps
> ? src/backend/port/.deps
> ? src/backend/postmaster/.deps
> ? src/backend/regex/.deps
> ? src/backend/rewrite/.deps
> ? src/backend/storage/buffer/.deps
> ? src/backend/storage/file/.deps
> ? src/backend/storage/freespace/.deps
> ? src/backend/storage/ipc/.deps
> ? src/backend/storage/large_object/.deps
> ? src/backend/storage/lmgr/.deps
> ? src/backend/storage/page/.deps
> ? src/backend/storage/smgr/.deps
> ? src/backend/tcop/.deps
> ? src/backend/utils/.deps
> ? src/backend/utils/adt/.deps
> ? src/backend/utils/cache/.deps
> ? src/backend/utils/error/.deps
> ? src/backend/utils/fmgr/.deps
> ? src/backend/utils/hash/.deps
> ? src/backend/utils/init/.deps
> ? src/backend/utils/mb/.deps
> ? src/backend/utils/mb/conversion_procs/conversion_create.sql
> ? src/backend/utils/mb/conversion_procs/ascii_and_mic/.deps
> ? src/backend/utils/mb/conversion_procs/ascii_and_mic/libascii_and_mic.so.0
> ? src/backend/utils/mb/conversion_procs/cyrillic_and_mic/.deps
> ? src/backend/utils/mb/conversion_procs/cyrillic_and_mic/libcyrillic_and_mic.so.0
> ? src/backend/utils/mb/conversion_procs/euc_cn_and_mic/.deps
> ? src/backend/utils/mb/conversion_procs/euc_cn_and_mic/libeuc_cn_and_mic.so.0
> ? src/backend/utils/mb/conversion_procs/euc_jp_and_sjis/.deps
> ? src/backend/utils/mb/conversion_procs/euc_jp_and_sjis/libeuc_jp_and_sjis.so.0
> ? src/backend/utils/mb/conversion_procs/euc_kr_and_mic/.deps
> ? src/backend/utils/mb/conversion_procs/euc_kr_and_mic/libeuc_kr_and_mic.so.0
> ? src/backend/utils/mb/conversion_procs/euc_tw_and_big5/.deps
> ? src/backend/utils/mb/conversion_procs/euc_tw_and_big5/libeuc_tw_and_big5.so.0
> ? src/backend/utils/mb/conversion_procs/latin2_and_win1250/.deps
> ? src/backend/utils/mb/conversion_procs/latin2_and_win1250/liblatin2_and_win1250.so.0
> ? src/backend/utils/mb/conversion_procs/latin_and_mic/.deps
> ? src/backend/utils/mb/conversion_procs/latin_and_mic/liblatin_and_mic.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_ascii/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_ascii/libutf8_and_ascii.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_big5/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_big5/libutf8_and_big5.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_cyrillic/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_cyrillic/libutf8_and_cyrillic.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_euc_cn/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_euc_cn/libutf8_and_euc_cn.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_euc_jp/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_euc_jp/libutf8_and_euc_jp.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_euc_kr/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_euc_kr/libutf8_and_euc_kr.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_euc_tw/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_euc_tw/libutf8_and_euc_tw.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_gb18030/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_gb18030/libutf8_and_gb18030.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_gbk/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_gbk/libutf8_and_gbk.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_iso8859/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_iso8859/libutf8_and_iso8859.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_iso8859_1/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_iso8859_1/libutf8_and_iso8859_1.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_johab/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_johab/libutf8_and_johab.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_sjis/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_sjis/libutf8_and_sjis.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_tcvn/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_tcvn/libutf8_and_tcvn.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_uhc/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_uhc/libutf8_and_uhc.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_win1250/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_win1250/libutf8_and_win1250.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_win1256/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_win1256/libutf8_and_win1256.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_win874/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_win874/libutf8_and_win874.so.0
> ? src/backend/utils/misc/.deps
> ? src/backend/utils/mmgr/.deps
> ? src/backend/utils/sort/.deps
> ? src/backend/utils/time/.deps
> ? src/bin/initdb/initdb
> ? src/bin/initlocation/initlocation
> ? src/bin/ipcclean/ipcclean
> ? src/bin/pg_config/pg_config
> ? src/bin/pg_controldata/.deps
> ? src/bin/pg_controldata/pg_controldata
> ? src/bin/pg_ctl/pg_ctl
> ? src/bin/pg_dump/.deps
> ? src/bin/pg_dump/pg_dump
> ? src/bin/pg_dump/pg_dump.core
> ? src/bin/pg_dump/pg_dumpall
> ? src/bin/pg_dump/pg_restore
> ? src/bin/pg_encoding/.deps
> ? src/bin/pg_encoding/pg_encoding
> ? src/bin/pg_id/.deps
> ? src/bin/pg_id/pg_id
> ? src/bin/pg_resetxlog/.deps
> ? src/bin/pg_resetxlog/pg_resetxlog
> ? src/bin/psql/.deps
> ? src/bin/psql/psql
> ? src/bin/scripts/.deps
> ? src/bin/scripts/clusterdb
> ? src/bin/scripts/createdb
> ? src/bin/scripts/createlang
> ? src/bin/scripts/createuser
> ? src/bin/scripts/dropdb
> ? src/bin/scripts/droplang
> ? src/bin/scripts/dropuser
> ? src/bin/scripts/vacuumdb
> ? src/include/pg_config.h
> ? src/include/stamp-h
> ? src/interfaces/ecpg/compatlib/.deps
> ? src/interfaces/ecpg/compatlib/libecpg_compat.so.1
> ? src/interfaces/ecpg/ecpglib/.deps
> ? src/interfaces/ecpg/ecpglib/libecpg.so.4
> ? src/interfaces/ecpg/pgtypeslib/.deps
> ? src/interfaces/ecpg/pgtypeslib/libpgtypes.so.1
> ? src/interfaces/ecpg/preproc/.deps
> ? src/interfaces/ecpg/preproc/ecpg
> ? src/interfaces/libpq/.deps
> ? src/interfaces/libpq/libpq.so.3
> ? src/pl/plperl/.deps
> ? src/pl/plperl/SPI.c
> ? src/pl/plperl/libplperl.so.0
> ? src/pl/plpgsql/src/.deps
> ? src/pl/plpgsql/src/libplpgsql.so.1
> ? src/port/.deps
> Index: src/bin/pg_dump/common.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/bin/pg_dump/common.c,v
> retrieving revision 1.75
> diff -c -r1.75 common.c
> *** src/bin/pg_dump/common.c    4 Aug 2003 02:40:09 -0000    1.75
> --- src/bin/pg_dump/common.c    20 Oct 2003 07:01:11 -0000
> ***************
> *** 61,66 ****
> --- 61,67 ----
>       int            numAggregates;
>       int            numOperators;
>       int            numOpclasses;
> +     int            numConversions;
>       NamespaceInfo *nsinfo;
>       TypeInfo   *tinfo;
>       FuncInfo   *finfo;
> ***************
> *** 69,74 ****
> --- 70,76 ----
>       InhInfo    *inhinfo;
>       OprInfo    *oprinfo;
>       OpclassInfo *opcinfo;
> +     ConvInfo *convinfo;
>
>       if (g_verbose)
>           write_msg(NULL, "reading schemas\n");
> ***************
> *** 95,100 ****
> --- 97,106 ----
>       opcinfo = getOpclasses(&numOpclasses);
>
>       if (g_verbose)
> +         write_msg(NULL, "reading user-defined conversions\n");
> +     convinfo = getConversions(&numConversions);
> +
> +     if (g_verbose)
>           write_msg(NULL, "reading user-defined tables\n");
>       tblinfo = getTables(&numTables);
>
> ***************
> *** 188,193 ****
> --- 194,206 ----
>           if (g_verbose)
>               write_msg(NULL, "dumping out user-defined casts\n");
>           dumpCasts(fout, finfo, numFuncs, tinfo, numTypes);
> +     }
> +
> +     if (!dataOnly)
> +     {
> +         if (g_verbose)
> +             write_msg(NULL, "dumping out user-defined conversions\n");
> +         dumpConversions(fout, convinfo, numConversions);
>       }
>
>       *numTablesPtr = numTables;
> Index: src/bin/pg_dump/pg_dump.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/bin/pg_dump/pg_dump.c,v
> retrieving revision 1.353
> diff -c -r1.353 pg_dump.c
> *** src/bin/pg_dump/pg_dump.c    8 Oct 2003 03:52:32 -0000    1.353
> --- src/bin/pg_dump/pg_dump.c    20 Oct 2003 07:01:13 -0000
> ***************
> *** 105,110 ****
> --- 105,111 ----
>   static const char *convertOperatorReference(const char *opr,
>                            OprInfo *g_oprinfo, int numOperators);
>   static void dumpOneOpclass(Archive *fout, OpclassInfo *opcinfo);
> + static void dumpOneConversion(Archive *fout, ConvInfo *convinfo);
>   static void dumpOneAgg(Archive *fout, AggInfo *agginfo);
>   static Oid    findLastBuiltinOid_V71(const char *);
>   static Oid    findLastBuiltinOid_V70(void);
> ***************
> *** 1690,1695 ****
> --- 1691,1769 ----
>   }
>
>   /*
> +  * getConversions:
> +  *      read all conversions in the system catalogs and return them in the
> +  * ConvInfo* structure
> +  *
> +  *    numConversions is set to the number of conversions read in
> +  */
> + ConvInfo *
> + getConversions(int *numConversions)
> + {
> +     PGresult   *res;
> +     int            ntups;
> +     int            i;
> +     PQExpBuffer query = createPQExpBuffer();
> +     ConvInfo *convinfo;
> +     int            i_oid;
> +     int            i_conname;
> +     int            i_connamespace;
> +     int            i_usename;
> +
> +     /* Conversions didn't exist pre-7.3 */
> +     if (g_fout->remoteVersion < 70300) {
> +         *numConversions = 0;
> +         return NULL;
> +     }
> +
> +     /*
> +      * find all conversions, including builtin conversions; we filter out
> +      * system-defined conversions at dump-out time.
> +      */
> +
> +     /* Make sure we are in proper schema */
> +     selectSourceSchema("pg_catalog");
> +
> +     appendPQExpBuffer(query, "SELECT pg_conversion.oid, conname, "
> +                       "connamespace, "
> +                       "(select usename from pg_user where conowner = usesysid) as usename "
> +                       "from pg_conversion");
> +
> +     res = PQexec(g_conn, query->data);
> +     if (!res ||
> +         PQresultStatus(res) != PGRES_TUPLES_OK)
> +     {
> +         write_msg(NULL, "query to obtain list of conversions failed: %s", PQerrorMessage(g_conn));
> +         exit_nicely();
> +     }
> +
> +     ntups = PQntuples(res);
> +     *numConversions = ntups;
> +
> +     convinfo = (ConvInfo *) malloc(ntups * sizeof(ConvInfo));
> +
> +     i_oid = PQfnumber(res, "oid");
> +     i_conname = PQfnumber(res, "conname");
> +     i_connamespace = PQfnumber(res, "connamespace");
> +     i_usename = PQfnumber(res, "usename");
> +
> +     for (i = 0; i < ntups; i++)
> +     {
> +         convinfo[i].oid = strdup(PQgetvalue(res, i, i_oid));
> +         convinfo[i].conname = strdup(PQgetvalue(res, i, i_conname));
> +         convinfo[i].connamespace = findNamespace(PQgetvalue(res, i, i_connamespace),
> +                                                 convinfo[i].oid);
> +         convinfo[i].usename = strdup(PQgetvalue(res, i, i_usename));
> +     }
> +
> +     PQclear(res);
> +
> +     destroyPQExpBuffer(query);
> +
> +     return convinfo;
> + }
> +
> + /*
>    * getOpclasses:
>    *      read all opclasses in the system catalogs and return them in the
>    * OpclassInfo* structure
> ***************
> *** 4624,4629 ****
> --- 4698,4827 ----
>       destroyPQExpBuffer(delq);
>   }
>
> + /*
> +  * dumpConversions
> +  *      writes out to fout the queries to create all the user-defined conversions
> +  */
> + void
> + dumpConversions(Archive *fout, ConvInfo convinfo[], int numConvs)
> + {
> +     int            i;
> +
> +     for (i = 0; i < numConvs; i++)
> +     {
> +         /* Dump only conversions in dumpable namespaces */
> +         if (!convinfo[i].connamespace->dump)
> +             continue;
> +
> +         dumpOneConversion(fout, &convinfo[i]);
> +     }
> + }
> +
> + /*
> +  * dumpOneConversion
> +  *      write out a single conversion definition
> +  */
> + static void
> + dumpOneConversion(Archive *fout, ConvInfo *convinfo)
> + {
> +     PQExpBuffer query = createPQExpBuffer();
> +     PQExpBuffer q = createPQExpBuffer();
> +     PQExpBuffer delq = createPQExpBuffer();
> +     PQExpBuffer details = createPQExpBuffer();
> +     PGresult   *res;
> +     int            ntups;
> +     int            i_conname;
> +     int            i_conforencoding;
> +     int            i_contoencoding;
> +     int            i_conproc;
> +     int            i_condefault;
> +     const char *conname;
> +     const char *conforencoding;
> +     const char *contoencoding;
> +     const char *conproc;
> +     bool        condefault;
> +
> +     /* Make sure we are in proper schema */
> +     selectSourceSchema(convinfo->connamespace->nspname);
> +
> +     /* Get conversion-specific details */
> +     appendPQExpBuffer(query, "SELECT conname,
> +                     pg_catalog.pg_encoding_to_char(conforencoding) AS conforencoding,
> +                     pg_catalog.pg_encoding_to_char(contoencoding) AS contoencoding,
> +                     conproc, condefault
> +                 FROM pg_catalog.pg_conversion c
> +                 WHERE c.oid = '%s'::pg_catalog.oid",
> +                     convinfo->oid);
> +
> +     res = PQexec(g_conn, query->data);
> +     if (!res ||
> +         PQresultStatus(res) != PGRES_TUPLES_OK)
> +     {
> +         write_msg(NULL, "query to obtain conversion failed: %s",
> +                   PQerrorMessage(g_conn));
> +         exit_nicely();
> +     }
> +
> +     /* Expecting a single result only */
> +     ntups = PQntuples(res);
> +     if (ntups != 1)
> +     {
> +         write_msg(NULL, "Got %d rows instead of one from: %s",
> +                   ntups, query->data);
> +         exit_nicely();
> +     }
> +
> +     i_conname = PQfnumber(res, "conname");
> +     i_conforencoding = PQfnumber(res, "conforencoding");
> +     i_contoencoding = PQfnumber(res, "contoencoding");
> +     i_conproc = PQfnumber(res, "conproc");
> +     i_condefault = PQfnumber(res, "condefault");
> +
> +     conname = PQgetvalue(res, 0, i_conname);
> +     conforencoding = PQgetvalue(res, 0, i_conforencoding);
> +     contoencoding = PQgetvalue(res, 0, i_contoencoding);
> +     conproc = PQgetvalue(res, 0, i_conproc);
> +     condefault = (PQgetvalue(res, 0, i_condefault)[0] == 't');
> +
> +     /*
> +      * DROP must be fully qualified in case same name appears in
> +      * pg_catalog
> +      */
> +     appendPQExpBuffer(delq, "DROP CONVERSION %s",
> +                       fmtId(convinfo->connamespace->nspname));
> +     appendPQExpBuffer(delq, ".%s;\n",
> +                       fmtId(convinfo->conname));
> +
> +     appendPQExpBuffer(q, "CREATE %sCONVERSION %s FOR ",
> +                     (condefault) ? "DEFAULT " : "",
> +                     fmtId(convinfo->conname));
> +     appendStringLiteral(q, conforencoding, true);
> +     appendPQExpBuffer(q, " TO ");
> +     appendStringLiteral(q, contoencoding, true);
> +     /* regproc is automatically quoted in 7.3 and above */
> +     appendPQExpBuffer(q, " FROM %s;\n", conproc);
> +
> +     ArchiveEntry(fout, convinfo->oid, convinfo->conname,
> +                  convinfo->connamespace->nspname, convinfo->usename,
> +                  "CONVERSION", NULL,
> +                  q->data, delq->data,
> +                  NULL, NULL, NULL);
> +
> +     /* Dump Conversion Comments */
> +
> +     resetPQExpBuffer(q);
> +     appendPQExpBuffer(q, "CONVERSION %s", convinfo->conname);
> +     dumpComment(fout, q->data,
> +                 convinfo->connamespace->nspname, convinfo->usename,
> +                 convinfo->oid, "pg_conversion", 0, NULL);
> +
> +     PQclear(res);
> +
> +     destroyPQExpBuffer(query);
> +     destroyPQExpBuffer(q);
> +     destroyPQExpBuffer(delq);
> +     destroyPQExpBuffer(details);
> + }
>
>   /*
>    * dumpAggs
> Index: src/bin/pg_dump/pg_dump.h
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/bin/pg_dump/pg_dump.h,v
> retrieving revision 1.104
> diff -c -r1.104 pg_dump.h
> *** src/bin/pg_dump/pg_dump.h    8 Aug 2003 04:52:21 -0000    1.104
> --- src/bin/pg_dump/pg_dump.h    20 Oct 2003 07:01:13 -0000
> ***************
> *** 95,100 ****
> --- 95,108 ----
>       char       *usename;
>   } OpclassInfo;
>
> + typedef struct _convInfo
> + {
> +     char       *oid;
> +     char       *conname;
> +     NamespaceInfo *connamespace;    /* link to containing namespace */
> +     char       *usename;
> + } ConvInfo;
> +
>   typedef struct _tableInfo
>   {
>       /*
> ***************
> *** 213,218 ****
> --- 221,227 ----
>   extern AggInfo *getAggregates(int *numAggregates);
>   extern OprInfo *getOperators(int *numOperators);
>   extern OpclassInfo *getOpclasses(int *numOpclasses);
> + extern ConvInfo *getConversions(int *numConversions);
>   extern TableInfo *getTables(int *numTables);
>   extern InhInfo *getInherits(int *numInherits);
>
> ***************
> *** 230,235 ****
> --- 239,246 ----
>   extern void dumpOprs(Archive *fout, OprInfo *oprinfo, int numOperators);
>   extern void dumpOpclasses(Archive *fout,
>                 OpclassInfo *opcinfo, int numOpclasses);
> + extern void dumpConversions(Archive *fout,
> +               ConvInfo *coninfo, int numConversions);
>   extern void dumpTables(Archive *fout, TableInfo tblinfo[], int numTables,
>              const bool aclsSkip,
>              const bool schemaOnly, const bool dataOnly);

>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Make pg_dump dump conversions

From

Tom Lane

Date:

01 December 2003, 04:09:39

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I will try to apply it within the next 48 hours.

This one's applied already, no?

            regards, tom lane

Re: Make pg_dump dump conversions

From

Bruce Momjian

Date:

01 December 2003, 11:52:12

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I will try to apply it within the next 48 hours.
>
> This one's applied already, no?

Yes, sorry.  Removed.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Make pg_dump dump conversions

From

Bruce Momjian

Date:

01 December 2003, 11:52:16

Patch already applied.

---------------------------------------------------------------------------

Christopher Kings-Lynne wrote:
> Just noticed a bug in the previous version of the patch.  Would fail
> against pre-7.3 postgres.
>
> Fixed in this new version.  Just discard the old.
>
> Chris
>
>
> Christopher Kings-Lynne wrote:
>
> > Save this for 7.5.  Nails a TODO item.
> >
> > Chris
>

> ? GNUmakefile
> ? config.log
> ? config.status
> ? src/Makefile.global
> ? src/backend/postgres
> ? src/backend/access/common/.deps
> ? src/backend/access/gist/.deps
> ? src/backend/access/hash/.deps
> ? src/backend/access/heap/.deps
> ? src/backend/access/index/.deps
> ? src/backend/access/nbtree/.deps
> ? src/backend/access/rtree/.deps
> ? src/backend/access/transam/.deps
> ? src/backend/bootstrap/.deps
> ? src/backend/catalog/.deps
> ? src/backend/catalog/postgres.bki
> ? src/backend/catalog/postgres.description
> ? src/backend/commands/.deps
> ? src/backend/executor/.deps
> ? src/backend/lib/.deps
> ? src/backend/libpq/.deps
> ? src/backend/main/.deps
> ? src/backend/nodes/.deps
> ? src/backend/optimizer/geqo/.deps
> ? src/backend/optimizer/path/.deps
> ? src/backend/optimizer/plan/.deps
> ? src/backend/optimizer/prep/.deps
> ? src/backend/optimizer/util/.deps
> ? src/backend/parser/.deps
> ? src/backend/port/.deps
> ? src/backend/postmaster/.deps
> ? src/backend/regex/.deps
> ? src/backend/rewrite/.deps
> ? src/backend/storage/buffer/.deps
> ? src/backend/storage/file/.deps
> ? src/backend/storage/freespace/.deps
> ? src/backend/storage/ipc/.deps
> ? src/backend/storage/large_object/.deps
> ? src/backend/storage/lmgr/.deps
> ? src/backend/storage/page/.deps
> ? src/backend/storage/smgr/.deps
> ? src/backend/tcop/.deps
> ? src/backend/utils/.deps
> ? src/backend/utils/adt/.deps
> ? src/backend/utils/cache/.deps
> ? src/backend/utils/error/.deps
> ? src/backend/utils/fmgr/.deps
> ? src/backend/utils/hash/.deps
> ? src/backend/utils/init/.deps
> ? src/backend/utils/mb/.deps
> ? src/backend/utils/mb/conversion_procs/conversion_create.sql
> ? src/backend/utils/mb/conversion_procs/ascii_and_mic/.deps
> ? src/backend/utils/mb/conversion_procs/ascii_and_mic/libascii_and_mic.so.0
> ? src/backend/utils/mb/conversion_procs/cyrillic_and_mic/.deps
> ? src/backend/utils/mb/conversion_procs/cyrillic_and_mic/libcyrillic_and_mic.so.0
> ? src/backend/utils/mb/conversion_procs/euc_cn_and_mic/.deps
> ? src/backend/utils/mb/conversion_procs/euc_cn_and_mic/libeuc_cn_and_mic.so.0
> ? src/backend/utils/mb/conversion_procs/euc_jp_and_sjis/.deps
> ? src/backend/utils/mb/conversion_procs/euc_jp_and_sjis/libeuc_jp_and_sjis.so.0
> ? src/backend/utils/mb/conversion_procs/euc_kr_and_mic/.deps
> ? src/backend/utils/mb/conversion_procs/euc_kr_and_mic/libeuc_kr_and_mic.so.0
> ? src/backend/utils/mb/conversion_procs/euc_tw_and_big5/.deps
> ? src/backend/utils/mb/conversion_procs/euc_tw_and_big5/libeuc_tw_and_big5.so.0
> ? src/backend/utils/mb/conversion_procs/latin2_and_win1250/.deps
> ? src/backend/utils/mb/conversion_procs/latin2_and_win1250/liblatin2_and_win1250.so.0
> ? src/backend/utils/mb/conversion_procs/latin_and_mic/.deps
> ? src/backend/utils/mb/conversion_procs/latin_and_mic/liblatin_and_mic.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_ascii/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_ascii/libutf8_and_ascii.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_big5/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_big5/libutf8_and_big5.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_cyrillic/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_cyrillic/libutf8_and_cyrillic.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_euc_cn/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_euc_cn/libutf8_and_euc_cn.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_euc_jp/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_euc_jp/libutf8_and_euc_jp.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_euc_kr/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_euc_kr/libutf8_and_euc_kr.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_euc_tw/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_euc_tw/libutf8_and_euc_tw.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_gb18030/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_gb18030/libutf8_and_gb18030.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_gbk/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_gbk/libutf8_and_gbk.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_iso8859/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_iso8859/libutf8_and_iso8859.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_iso8859_1/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_iso8859_1/libutf8_and_iso8859_1.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_johab/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_johab/libutf8_and_johab.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_sjis/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_sjis/libutf8_and_sjis.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_tcvn/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_tcvn/libutf8_and_tcvn.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_uhc/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_uhc/libutf8_and_uhc.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_win1250/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_win1250/libutf8_and_win1250.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_win1256/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_win1256/libutf8_and_win1256.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_win874/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_win874/libutf8_and_win874.so.0
> ? src/backend/utils/misc/.deps
> ? src/backend/utils/mmgr/.deps
> ? src/backend/utils/sort/.deps
> ? src/backend/utils/time/.deps
> ? src/bin/initdb/initdb
> ? src/bin/initlocation/initlocation
> ? src/bin/ipcclean/ipcclean
> ? src/bin/pg_config/pg_config
> ? src/bin/pg_controldata/.deps
> ? src/bin/pg_controldata/pg_controldata
> ? src/bin/pg_ctl/pg_ctl
> ? src/bin/pg_dump/.deps
> ? src/bin/pg_dump/pg_dump
> ? src/bin/pg_dump/pg_dump.core
> ? src/bin/pg_dump/pg_dumpall
> ? src/bin/pg_dump/pg_restore
> ? src/bin/pg_encoding/.deps
> ? src/bin/pg_encoding/pg_encoding
> ? src/bin/pg_id/.deps
> ? src/bin/pg_id/pg_id
> ? src/bin/pg_resetxlog/.deps
> ? src/bin/pg_resetxlog/pg_resetxlog
> ? src/bin/psql/.deps
> ? src/bin/psql/psql
> ? src/bin/scripts/.deps
> ? src/bin/scripts/clusterdb
> ? src/bin/scripts/createdb
> ? src/bin/scripts/createlang
> ? src/bin/scripts/createuser
> ? src/bin/scripts/dropdb
> ? src/bin/scripts/droplang
> ? src/bin/scripts/dropuser
> ? src/bin/scripts/vacuumdb
> ? src/include/pg_config.h
> ? src/include/stamp-h
> ? src/interfaces/ecpg/compatlib/.deps
> ? src/interfaces/ecpg/compatlib/libecpg_compat.so.1
> ? src/interfaces/ecpg/ecpglib/.deps
> ? src/interfaces/ecpg/ecpglib/libecpg.so.4
> ? src/interfaces/ecpg/pgtypeslib/.deps
> ? src/interfaces/ecpg/pgtypeslib/libpgtypes.so.1
> ? src/interfaces/ecpg/preproc/.deps
> ? src/interfaces/ecpg/preproc/ecpg
> ? src/interfaces/libpq/.deps
> ? src/interfaces/libpq/libpq.so.3
> ? src/pl/plperl/.deps
> ? src/pl/plperl/SPI.c
> ? src/pl/plperl/libplperl.so.0
> ? src/pl/plpgsql/src/.deps
> ? src/pl/plpgsql/src/libplpgsql.so.1
> ? src/port/.deps
> Index: src/bin/pg_dump/common.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/bin/pg_dump/common.c,v
> retrieving revision 1.75
> diff -c -r1.75 common.c
> *** src/bin/pg_dump/common.c    4 Aug 2003 02:40:09 -0000    1.75
> --- src/bin/pg_dump/common.c    20 Oct 2003 07:01:11 -0000
> ***************
> *** 61,66 ****
> --- 61,67 ----
>       int            numAggregates;
>       int            numOperators;
>       int            numOpclasses;
> +     int            numConversions;
>       NamespaceInfo *nsinfo;
>       TypeInfo   *tinfo;
>       FuncInfo   *finfo;
> ***************
> *** 69,74 ****
> --- 70,76 ----
>       InhInfo    *inhinfo;
>       OprInfo    *oprinfo;
>       OpclassInfo *opcinfo;
> +     ConvInfo *convinfo;
>
>       if (g_verbose)
>           write_msg(NULL, "reading schemas\n");
> ***************
> *** 95,100 ****
> --- 97,106 ----
>       opcinfo = getOpclasses(&numOpclasses);
>
>       if (g_verbose)
> +         write_msg(NULL, "reading user-defined conversions\n");
> +     convinfo = getConversions(&numConversions);
> +
> +     if (g_verbose)
>           write_msg(NULL, "reading user-defined tables\n");
>       tblinfo = getTables(&numTables);
>
> ***************
> *** 188,193 ****
> --- 194,206 ----
>           if (g_verbose)
>               write_msg(NULL, "dumping out user-defined casts\n");
>           dumpCasts(fout, finfo, numFuncs, tinfo, numTypes);
> +     }
> +
> +     if (!dataOnly)
> +     {
> +         if (g_verbose)
> +             write_msg(NULL, "dumping out user-defined conversions\n");
> +         dumpConversions(fout, convinfo, numConversions);
>       }
>
>       *numTablesPtr = numTables;
> Index: src/bin/pg_dump/pg_dump.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/bin/pg_dump/pg_dump.c,v
> retrieving revision 1.353
> diff -c -r1.353 pg_dump.c
> *** src/bin/pg_dump/pg_dump.c    8 Oct 2003 03:52:32 -0000    1.353
> --- src/bin/pg_dump/pg_dump.c    20 Oct 2003 07:01:13 -0000
> ***************
> *** 105,110 ****
> --- 105,111 ----
>   static const char *convertOperatorReference(const char *opr,
>                            OprInfo *g_oprinfo, int numOperators);
>   static void dumpOneOpclass(Archive *fout, OpclassInfo *opcinfo);
> + static void dumpOneConversion(Archive *fout, ConvInfo *convinfo);
>   static void dumpOneAgg(Archive *fout, AggInfo *agginfo);
>   static Oid    findLastBuiltinOid_V71(const char *);
>   static Oid    findLastBuiltinOid_V70(void);
> ***************
> *** 1690,1695 ****
> --- 1691,1769 ----
>   }
>
>   /*
> +  * getConversions:
> +  *      read all conversions in the system catalogs and return them in the
> +  * ConvInfo* structure
> +  *
> +  *    numConversions is set to the number of conversions read in
> +  */
> + ConvInfo *
> + getConversions(int *numConversions)
> + {
> +     PGresult   *res;
> +     int            ntups;
> +     int            i;
> +     PQExpBuffer query = createPQExpBuffer();
> +     ConvInfo *convinfo;
> +     int            i_oid;
> +     int            i_conname;
> +     int            i_connamespace;
> +     int            i_usename;
> +
> +     /* Conversions didn't exist pre-7.3 */
> +     if (g_fout->remoteVersion < 70300) {
> +         *numConversions = 0;
> +         return NULL;
> +     }
> +
> +     /*
> +      * find all conversions, including builtin conversions; we filter out
> +      * system-defined conversions at dump-out time.
> +      */
> +
> +     /* Make sure we are in proper schema */
> +     selectSourceSchema("pg_catalog");
> +
> +     appendPQExpBuffer(query, "SELECT pg_conversion.oid, conname, "
> +                       "connamespace, "
> +                       "(select usename from pg_user where conowner = usesysid) as usename "
> +                       "from pg_conversion");
> +
> +     res = PQexec(g_conn, query->data);
> +     if (!res ||
> +         PQresultStatus(res) != PGRES_TUPLES_OK)
> +     {
> +         write_msg(NULL, "query to obtain list of conversions failed: %s", PQerrorMessage(g_conn));
> +         exit_nicely();
> +     }
> +
> +     ntups = PQntuples(res);
> +     *numConversions = ntups;
> +
> +     convinfo = (ConvInfo *) malloc(ntups * sizeof(ConvInfo));
> +
> +     i_oid = PQfnumber(res, "oid");
> +     i_conname = PQfnumber(res, "conname");
> +     i_connamespace = PQfnumber(res, "connamespace");
> +     i_usename = PQfnumber(res, "usename");
> +
> +     for (i = 0; i < ntups; i++)
> +     {
> +         convinfo[i].oid = strdup(PQgetvalue(res, i, i_oid));
> +         convinfo[i].conname = strdup(PQgetvalue(res, i, i_conname));
> +         convinfo[i].connamespace = findNamespace(PQgetvalue(res, i, i_connamespace),
> +                                                 convinfo[i].oid);
> +         convinfo[i].usename = strdup(PQgetvalue(res, i, i_usename));
> +     }
> +
> +     PQclear(res);
> +
> +     destroyPQExpBuffer(query);
> +
> +     return convinfo;
> + }
> +
> + /*
>    * getOpclasses:
>    *      read all opclasses in the system catalogs and return them in the
>    * OpclassInfo* structure
> ***************
> *** 4624,4629 ****
> --- 4698,4827 ----
>       destroyPQExpBuffer(delq);
>   }
>
> + /*
> +  * dumpConversions
> +  *      writes out to fout the queries to create all the user-defined conversions
> +  */
> + void
> + dumpConversions(Archive *fout, ConvInfo convinfo[], int numConvs)
> + {
> +     int            i;
> +
> +     for (i = 0; i < numConvs; i++)
> +     {
> +         /* Dump only conversions in dumpable namespaces */
> +         if (!convinfo[i].connamespace->dump)
> +             continue;
> +
> +         dumpOneConversion(fout, &convinfo[i]);
> +     }
> + }
> +
> + /*
> +  * dumpOneConversion
> +  *      write out a single conversion definition
> +  */
> + static void
> + dumpOneConversion(Archive *fout, ConvInfo *convinfo)
> + {
> +     PQExpBuffer query = createPQExpBuffer();
> +     PQExpBuffer q = createPQExpBuffer();
> +     PQExpBuffer delq = createPQExpBuffer();
> +     PQExpBuffer details = createPQExpBuffer();
> +     PGresult   *res;
> +     int            ntups;
> +     int            i_conname;
> +     int            i_conforencoding;
> +     int            i_contoencoding;
> +     int            i_conproc;
> +     int            i_condefault;
> +     const char *conname;
> +     const char *conforencoding;
> +     const char *contoencoding;
> +     const char *conproc;
> +     bool        condefault;
> +
> +     /* Make sure we are in proper schema */
> +     selectSourceSchema(convinfo->connamespace->nspname);
> +
> +     /* Get conversion-specific details */
> +     appendPQExpBuffer(query, "SELECT conname,
> +                     pg_catalog.pg_encoding_to_char(conforencoding) AS conforencoding,
> +                     pg_catalog.pg_encoding_to_char(contoencoding) AS contoencoding,
> +                     conproc, condefault
> +                 FROM pg_catalog.pg_conversion c
> +                 WHERE c.oid = '%s'::pg_catalog.oid",
> +                     convinfo->oid);
> +
> +     res = PQexec(g_conn, query->data);
> +     if (!res ||
> +         PQresultStatus(res) != PGRES_TUPLES_OK)
> +     {
> +         write_msg(NULL, "query to obtain conversion failed: %s",
> +                   PQerrorMessage(g_conn));
> +         exit_nicely();
> +     }
> +
> +     /* Expecting a single result only */
> +     ntups = PQntuples(res);
> +     if (ntups != 1)
> +     {
> +         write_msg(NULL, "Got %d rows instead of one from: %s",
> +                   ntups, query->data);
> +         exit_nicely();
> +     }
> +
> +     i_conname = PQfnumber(res, "conname");
> +     i_conforencoding = PQfnumber(res, "conforencoding");
> +     i_contoencoding = PQfnumber(res, "contoencoding");
> +     i_conproc = PQfnumber(res, "conproc");
> +     i_condefault = PQfnumber(res, "condefault");
> +
> +     conname = PQgetvalue(res, 0, i_conname);
> +     conforencoding = PQgetvalue(res, 0, i_conforencoding);
> +     contoencoding = PQgetvalue(res, 0, i_contoencoding);
> +     conproc = PQgetvalue(res, 0, i_conproc);
> +     condefault = (PQgetvalue(res, 0, i_condefault)[0] == 't');
> +
> +     /*
> +      * DROP must be fully qualified in case same name appears in
> +      * pg_catalog
> +      */
> +     appendPQExpBuffer(delq, "DROP CONVERSION %s",
> +                       fmtId(convinfo->connamespace->nspname));
> +     appendPQExpBuffer(delq, ".%s;\n",
> +                       fmtId(convinfo->conname));
> +
> +     appendPQExpBuffer(q, "CREATE %sCONVERSION %s FOR ",
> +                     (condefault) ? "DEFAULT " : "",
> +                     fmtId(convinfo->conname));
> +     appendStringLiteral(q, conforencoding, true);
> +     appendPQExpBuffer(q, " TO ");
> +     appendStringLiteral(q, contoencoding, true);
> +     /* regproc is automatically quoted in 7.3 and above */
> +     appendPQExpBuffer(q, " FROM %s;\n", conproc);
> +
> +     ArchiveEntry(fout, convinfo->oid, convinfo->conname,
> +                  convinfo->connamespace->nspname, convinfo->usename,
> +                  "CONVERSION", NULL,
> +                  q->data, delq->data,
> +                  NULL, NULL, NULL);
> +
> +     /* Dump Conversion Comments */
> +
> +     resetPQExpBuffer(q);
> +     appendPQExpBuffer(q, "CONVERSION %s", convinfo->conname);
> +     dumpComment(fout, q->data,
> +                 convinfo->connamespace->nspname, convinfo->usename,
> +                 convinfo->oid, "pg_conversion", 0, NULL);
> +
> +     PQclear(res);
> +
> +     destroyPQExpBuffer(query);
> +     destroyPQExpBuffer(q);
> +     destroyPQExpBuffer(delq);
> +     destroyPQExpBuffer(details);
> + }
>
>   /*
>    * dumpAggs
> Index: src/bin/pg_dump/pg_dump.h
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/bin/pg_dump/pg_dump.h,v
> retrieving revision 1.104
> diff -c -r1.104 pg_dump.h
> *** src/bin/pg_dump/pg_dump.h    8 Aug 2003 04:52:21 -0000    1.104
> --- src/bin/pg_dump/pg_dump.h    20 Oct 2003 07:01:13 -0000
> ***************
> *** 95,100 ****
> --- 95,108 ----
>       char       *usename;
>   } OpclassInfo;
>
> + typedef struct _convInfo
> + {
> +     char       *oid;
> +     char       *conname;
> +     NamespaceInfo *connamespace;    /* link to containing namespace */
> +     char       *usename;
> + } ConvInfo;
> +
>   typedef struct _tableInfo
>   {
>       /*
> ***************
> *** 213,218 ****
> --- 221,227 ----
>   extern AggInfo *getAggregates(int *numAggregates);
>   extern OprInfo *getOperators(int *numOperators);
>   extern OpclassInfo *getOpclasses(int *numOpclasses);
> + extern ConvInfo *getConversions(int *numConversions);
>   extern TableInfo *getTables(int *numTables);
>   extern InhInfo *getInherits(int *numInherits);
>
> ***************
> *** 230,235 ****
> --- 239,246 ----
>   extern void dumpOprs(Archive *fout, OprInfo *oprinfo, int numOperators);
>   extern void dumpOpclasses(Archive *fout,
>                 OpclassInfo *opcinfo, int numOpclasses);
> + extern void dumpConversions(Archive *fout,
> +               ConvInfo *coninfo, int numConversions);
>   extern void dumpTables(Archive *fout, TableInfo tblinfo[], int numTables,
>              const bool aclsSkip,
>              const bool schemaOnly, const bool dataOnly);

>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Make pg_dump dump conversions

From

Bruce Momjian

Date:

01 December 2003, 21:16:03

Just to confirm --- this was applied to 7.4 already.

---------------------------------------------------------------------------

Christopher Kings-Lynne wrote:
> Just noticed a bug in the previous version of the patch.  Would fail
> against pre-7.3 postgres.
>
> Fixed in this new version.  Just discard the old.
>
> Chris
>
>
> Christopher Kings-Lynne wrote:
>
> > Save this for 7.5.  Nails a TODO item.
> >
> > Chris
>

> ? GNUmakefile
> ? config.log
> ? config.status
> ? src/Makefile.global
> ? src/backend/postgres
> ? src/backend/access/common/.deps
> ? src/backend/access/gist/.deps
> ? src/backend/access/hash/.deps
> ? src/backend/access/heap/.deps
> ? src/backend/access/index/.deps
> ? src/backend/access/nbtree/.deps
> ? src/backend/access/rtree/.deps
> ? src/backend/access/transam/.deps
> ? src/backend/bootstrap/.deps
> ? src/backend/catalog/.deps
> ? src/backend/catalog/postgres.bki
> ? src/backend/catalog/postgres.description
> ? src/backend/commands/.deps
> ? src/backend/executor/.deps
> ? src/backend/lib/.deps
> ? src/backend/libpq/.deps
> ? src/backend/main/.deps
> ? src/backend/nodes/.deps
> ? src/backend/optimizer/geqo/.deps
> ? src/backend/optimizer/path/.deps
> ? src/backend/optimizer/plan/.deps
> ? src/backend/optimizer/prep/.deps
> ? src/backend/optimizer/util/.deps
> ? src/backend/parser/.deps
> ? src/backend/port/.deps
> ? src/backend/postmaster/.deps
> ? src/backend/regex/.deps
> ? src/backend/rewrite/.deps
> ? src/backend/storage/buffer/.deps
> ? src/backend/storage/file/.deps
> ? src/backend/storage/freespace/.deps
> ? src/backend/storage/ipc/.deps
> ? src/backend/storage/large_object/.deps
> ? src/backend/storage/lmgr/.deps
> ? src/backend/storage/page/.deps
> ? src/backend/storage/smgr/.deps
> ? src/backend/tcop/.deps
> ? src/backend/utils/.deps
> ? src/backend/utils/adt/.deps
> ? src/backend/utils/cache/.deps
> ? src/backend/utils/error/.deps
> ? src/backend/utils/fmgr/.deps
> ? src/backend/utils/hash/.deps
> ? src/backend/utils/init/.deps
> ? src/backend/utils/mb/.deps
> ? src/backend/utils/mb/conversion_procs/conversion_create.sql
> ? src/backend/utils/mb/conversion_procs/ascii_and_mic/.deps
> ? src/backend/utils/mb/conversion_procs/ascii_and_mic/libascii_and_mic.so.0
> ? src/backend/utils/mb/conversion_procs/cyrillic_and_mic/.deps
> ? src/backend/utils/mb/conversion_procs/cyrillic_and_mic/libcyrillic_and_mic.so.0
> ? src/backend/utils/mb/conversion_procs/euc_cn_and_mic/.deps
> ? src/backend/utils/mb/conversion_procs/euc_cn_and_mic/libeuc_cn_and_mic.so.0
> ? src/backend/utils/mb/conversion_procs/euc_jp_and_sjis/.deps
> ? src/backend/utils/mb/conversion_procs/euc_jp_and_sjis/libeuc_jp_and_sjis.so.0
> ? src/backend/utils/mb/conversion_procs/euc_kr_and_mic/.deps
> ? src/backend/utils/mb/conversion_procs/euc_kr_and_mic/libeuc_kr_and_mic.so.0
> ? src/backend/utils/mb/conversion_procs/euc_tw_and_big5/.deps
> ? src/backend/utils/mb/conversion_procs/euc_tw_and_big5/libeuc_tw_and_big5.so.0
> ? src/backend/utils/mb/conversion_procs/latin2_and_win1250/.deps
> ? src/backend/utils/mb/conversion_procs/latin2_and_win1250/liblatin2_and_win1250.so.0
> ? src/backend/utils/mb/conversion_procs/latin_and_mic/.deps
> ? src/backend/utils/mb/conversion_procs/latin_and_mic/liblatin_and_mic.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_ascii/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_ascii/libutf8_and_ascii.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_big5/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_big5/libutf8_and_big5.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_cyrillic/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_cyrillic/libutf8_and_cyrillic.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_euc_cn/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_euc_cn/libutf8_and_euc_cn.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_euc_jp/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_euc_jp/libutf8_and_euc_jp.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_euc_kr/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_euc_kr/libutf8_and_euc_kr.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_euc_tw/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_euc_tw/libutf8_and_euc_tw.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_gb18030/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_gb18030/libutf8_and_gb18030.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_gbk/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_gbk/libutf8_and_gbk.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_iso8859/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_iso8859/libutf8_and_iso8859.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_iso8859_1/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_iso8859_1/libutf8_and_iso8859_1.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_johab/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_johab/libutf8_and_johab.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_sjis/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_sjis/libutf8_and_sjis.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_tcvn/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_tcvn/libutf8_and_tcvn.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_uhc/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_uhc/libutf8_and_uhc.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_win1250/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_win1250/libutf8_and_win1250.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_win1256/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_win1256/libutf8_and_win1256.so.0
> ? src/backend/utils/mb/conversion_procs/utf8_and_win874/.deps
> ? src/backend/utils/mb/conversion_procs/utf8_and_win874/libutf8_and_win874.so.0
> ? src/backend/utils/misc/.deps
> ? src/backend/utils/mmgr/.deps
> ? src/backend/utils/sort/.deps
> ? src/backend/utils/time/.deps
> ? src/bin/initdb/initdb
> ? src/bin/initlocation/initlocation
> ? src/bin/ipcclean/ipcclean
> ? src/bin/pg_config/pg_config
> ? src/bin/pg_controldata/.deps
> ? src/bin/pg_controldata/pg_controldata
> ? src/bin/pg_ctl/pg_ctl
> ? src/bin/pg_dump/.deps
> ? src/bin/pg_dump/pg_dump
> ? src/bin/pg_dump/pg_dump.core
> ? src/bin/pg_dump/pg_dumpall
> ? src/bin/pg_dump/pg_restore
> ? src/bin/pg_encoding/.deps
> ? src/bin/pg_encoding/pg_encoding
> ? src/bin/pg_id/.deps
> ? src/bin/pg_id/pg_id
> ? src/bin/pg_resetxlog/.deps
> ? src/bin/pg_resetxlog/pg_resetxlog
> ? src/bin/psql/.deps
> ? src/bin/psql/psql
> ? src/bin/scripts/.deps
> ? src/bin/scripts/clusterdb
> ? src/bin/scripts/createdb
> ? src/bin/scripts/createlang
> ? src/bin/scripts/createuser
> ? src/bin/scripts/dropdb
> ? src/bin/scripts/droplang
> ? src/bin/scripts/dropuser
> ? src/bin/scripts/vacuumdb
> ? src/include/pg_config.h
> ? src/include/stamp-h
> ? src/interfaces/ecpg/compatlib/.deps
> ? src/interfaces/ecpg/compatlib/libecpg_compat.so.1
> ? src/interfaces/ecpg/ecpglib/.deps
> ? src/interfaces/ecpg/ecpglib/libecpg.so.4
> ? src/interfaces/ecpg/pgtypeslib/.deps
> ? src/interfaces/ecpg/pgtypeslib/libpgtypes.so.1
> ? src/interfaces/ecpg/preproc/.deps
> ? src/interfaces/ecpg/preproc/ecpg
> ? src/interfaces/libpq/.deps
> ? src/interfaces/libpq/libpq.so.3
> ? src/pl/plperl/.deps
> ? src/pl/plperl/SPI.c
> ? src/pl/plperl/libplperl.so.0
> ? src/pl/plpgsql/src/.deps
> ? src/pl/plpgsql/src/libplpgsql.so.1
> ? src/port/.deps
> Index: src/bin/pg_dump/common.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/bin/pg_dump/common.c,v
> retrieving revision 1.75
> diff -c -r1.75 common.c
> *** src/bin/pg_dump/common.c    4 Aug 2003 02:40:09 -0000    1.75
> --- src/bin/pg_dump/common.c    20 Oct 2003 07:01:11 -0000
> ***************
> *** 61,66 ****
> --- 61,67 ----
>       int            numAggregates;
>       int            numOperators;
>       int            numOpclasses;
> +     int            numConversions;
>       NamespaceInfo *nsinfo;
>       TypeInfo   *tinfo;
>       FuncInfo   *finfo;
> ***************
> *** 69,74 ****
> --- 70,76 ----
>       InhInfo    *inhinfo;
>       OprInfo    *oprinfo;
>       OpclassInfo *opcinfo;
> +     ConvInfo *convinfo;
>
>       if (g_verbose)
>           write_msg(NULL, "reading schemas\n");
> ***************
> *** 95,100 ****
> --- 97,106 ----
>       opcinfo = getOpclasses(&numOpclasses);
>
>       if (g_verbose)
> +         write_msg(NULL, "reading user-defined conversions\n");
> +     convinfo = getConversions(&numConversions);
> +
> +     if (g_verbose)
>           write_msg(NULL, "reading user-defined tables\n");
>       tblinfo = getTables(&numTables);
>
> ***************
> *** 188,193 ****
> --- 194,206 ----
>           if (g_verbose)
>               write_msg(NULL, "dumping out user-defined casts\n");
>           dumpCasts(fout, finfo, numFuncs, tinfo, numTypes);
> +     }
> +
> +     if (!dataOnly)
> +     {
> +         if (g_verbose)
> +             write_msg(NULL, "dumping out user-defined conversions\n");
> +         dumpConversions(fout, convinfo, numConversions);
>       }
>
>       *numTablesPtr = numTables;
> Index: src/bin/pg_dump/pg_dump.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/bin/pg_dump/pg_dump.c,v
> retrieving revision 1.353
> diff -c -r1.353 pg_dump.c
> *** src/bin/pg_dump/pg_dump.c    8 Oct 2003 03:52:32 -0000    1.353
> --- src/bin/pg_dump/pg_dump.c    20 Oct 2003 07:01:13 -0000
> ***************
> *** 105,110 ****
> --- 105,111 ----
>   static const char *convertOperatorReference(const char *opr,
>                            OprInfo *g_oprinfo, int numOperators);
>   static void dumpOneOpclass(Archive *fout, OpclassInfo *opcinfo);
> + static void dumpOneConversion(Archive *fout, ConvInfo *convinfo);
>   static void dumpOneAgg(Archive *fout, AggInfo *agginfo);
>   static Oid    findLastBuiltinOid_V71(const char *);
>   static Oid    findLastBuiltinOid_V70(void);
> ***************
> *** 1690,1695 ****
> --- 1691,1769 ----
>   }
>
>   /*
> +  * getConversions:
> +  *      read all conversions in the system catalogs and return them in the
> +  * ConvInfo* structure
> +  *
> +  *    numConversions is set to the number of conversions read in
> +  */
> + ConvInfo *
> + getConversions(int *numConversions)
> + {
> +     PGresult   *res;
> +     int            ntups;
> +     int            i;
> +     PQExpBuffer query = createPQExpBuffer();
> +     ConvInfo *convinfo;
> +     int            i_oid;
> +     int            i_conname;
> +     int            i_connamespace;
> +     int            i_usename;
> +
> +     /* Conversions didn't exist pre-7.3 */
> +     if (g_fout->remoteVersion < 70300) {
> +         *numConversions = 0;
> +         return NULL;
> +     }
> +
> +     /*
> +      * find all conversions, including builtin conversions; we filter out
> +      * system-defined conversions at dump-out time.
> +      */
> +
> +     /* Make sure we are in proper schema */
> +     selectSourceSchema("pg_catalog");
> +
> +     appendPQExpBuffer(query, "SELECT pg_conversion.oid, conname, "
> +                       "connamespace, "
> +                       "(select usename from pg_user where conowner = usesysid) as usename "
> +                       "from pg_conversion");
> +
> +     res = PQexec(g_conn, query->data);
> +     if (!res ||
> +         PQresultStatus(res) != PGRES_TUPLES_OK)
> +     {
> +         write_msg(NULL, "query to obtain list of conversions failed: %s", PQerrorMessage(g_conn));
> +         exit_nicely();
> +     }
> +
> +     ntups = PQntuples(res);
> +     *numConversions = ntups;
> +
> +     convinfo = (ConvInfo *) malloc(ntups * sizeof(ConvInfo));
> +
> +     i_oid = PQfnumber(res, "oid");
> +     i_conname = PQfnumber(res, "conname");
> +     i_connamespace = PQfnumber(res, "connamespace");
> +     i_usename = PQfnumber(res, "usename");
> +
> +     for (i = 0; i < ntups; i++)
> +     {
> +         convinfo[i].oid = strdup(PQgetvalue(res, i, i_oid));
> +         convinfo[i].conname = strdup(PQgetvalue(res, i, i_conname));
> +         convinfo[i].connamespace = findNamespace(PQgetvalue(res, i, i_connamespace),
> +                                                 convinfo[i].oid);
> +         convinfo[i].usename = strdup(PQgetvalue(res, i, i_usename));
> +     }
> +
> +     PQclear(res);
> +
> +     destroyPQExpBuffer(query);
> +
> +     return convinfo;
> + }
> +
> + /*
>    * getOpclasses:
>    *      read all opclasses in the system catalogs and return them in the
>    * OpclassInfo* structure
> ***************
> *** 4624,4629 ****
> --- 4698,4827 ----
>       destroyPQExpBuffer(delq);
>   }
>
> + /*
> +  * dumpConversions
> +  *      writes out to fout the queries to create all the user-defined conversions
> +  */
> + void
> + dumpConversions(Archive *fout, ConvInfo convinfo[], int numConvs)
> + {
> +     int            i;
> +
> +     for (i = 0; i < numConvs; i++)
> +     {
> +         /* Dump only conversions in dumpable namespaces */
> +         if (!convinfo[i].connamespace->dump)
> +             continue;
> +
> +         dumpOneConversion(fout, &convinfo[i]);
> +     }
> + }
> +
> + /*
> +  * dumpOneConversion
> +  *      write out a single conversion definition
> +  */
> + static void
> + dumpOneConversion(Archive *fout, ConvInfo *convinfo)
> + {
> +     PQExpBuffer query = createPQExpBuffer();
> +     PQExpBuffer q = createPQExpBuffer();
> +     PQExpBuffer delq = createPQExpBuffer();
> +     PQExpBuffer details = createPQExpBuffer();
> +     PGresult   *res;
> +     int            ntups;
> +     int            i_conname;
> +     int            i_conforencoding;
> +     int            i_contoencoding;
> +     int            i_conproc;
> +     int            i_condefault;
> +     const char *conname;
> +     const char *conforencoding;
> +     const char *contoencoding;
> +     const char *conproc;
> +     bool        condefault;
> +
> +     /* Make sure we are in proper schema */
> +     selectSourceSchema(convinfo->connamespace->nspname);
> +
> +     /* Get conversion-specific details */
> +     appendPQExpBuffer(query, "SELECT conname,
> +                     pg_catalog.pg_encoding_to_char(conforencoding) AS conforencoding,
> +                     pg_catalog.pg_encoding_to_char(contoencoding) AS contoencoding,
> +                     conproc, condefault
> +                 FROM pg_catalog.pg_conversion c
> +                 WHERE c.oid = '%s'::pg_catalog.oid",
> +                     convinfo->oid);
> +
> +     res = PQexec(g_conn, query->data);
> +     if (!res ||
> +         PQresultStatus(res) != PGRES_TUPLES_OK)
> +     {
> +         write_msg(NULL, "query to obtain conversion failed: %s",
> +                   PQerrorMessage(g_conn));
> +         exit_nicely();
> +     }
> +
> +     /* Expecting a single result only */
> +     ntups = PQntuples(res);
> +     if (ntups != 1)
> +     {
> +         write_msg(NULL, "Got %d rows instead of one from: %s",
> +                   ntups, query->data);
> +         exit_nicely();
> +     }
> +
> +     i_conname = PQfnumber(res, "conname");
> +     i_conforencoding = PQfnumber(res, "conforencoding");
> +     i_contoencoding = PQfnumber(res, "contoencoding");
> +     i_conproc = PQfnumber(res, "conproc");
> +     i_condefault = PQfnumber(res, "condefault");
> +
> +     conname = PQgetvalue(res, 0, i_conname);
> +     conforencoding = PQgetvalue(res, 0, i_conforencoding);
> +     contoencoding = PQgetvalue(res, 0, i_contoencoding);
> +     conproc = PQgetvalue(res, 0, i_conproc);
> +     condefault = (PQgetvalue(res, 0, i_condefault)[0] == 't');
> +
> +     /*
> +      * DROP must be fully qualified in case same name appears in
> +      * pg_catalog
> +      */
> +     appendPQExpBuffer(delq, "DROP CONVERSION %s",
> +                       fmtId(convinfo->connamespace->nspname));
> +     appendPQExpBuffer(delq, ".%s;\n",
> +                       fmtId(convinfo->conname));
> +
> +     appendPQExpBuffer(q, "CREATE %sCONVERSION %s FOR ",
> +                     (condefault) ? "DEFAULT " : "",
> +                     fmtId(convinfo->conname));
> +     appendStringLiteral(q, conforencoding, true);
> +     appendPQExpBuffer(q, " TO ");
> +     appendStringLiteral(q, contoencoding, true);
> +     /* regproc is automatically quoted in 7.3 and above */
> +     appendPQExpBuffer(q, " FROM %s;\n", conproc);
> +
> +     ArchiveEntry(fout, convinfo->oid, convinfo->conname,
> +                  convinfo->connamespace->nspname, convinfo->usename,
> +                  "CONVERSION", NULL,
> +                  q->data, delq->data,
> +                  NULL, NULL, NULL);
> +
> +     /* Dump Conversion Comments */
> +
> +     resetPQExpBuffer(q);
> +     appendPQExpBuffer(q, "CONVERSION %s", convinfo->conname);
> +     dumpComment(fout, q->data,
> +                 convinfo->connamespace->nspname, convinfo->usename,
> +                 convinfo->oid, "pg_conversion", 0, NULL);
> +
> +     PQclear(res);
> +
> +     destroyPQExpBuffer(query);
> +     destroyPQExpBuffer(q);
> +     destroyPQExpBuffer(delq);
> +     destroyPQExpBuffer(details);
> + }
>
>   /*
>    * dumpAggs
> Index: src/bin/pg_dump/pg_dump.h
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/bin/pg_dump/pg_dump.h,v
> retrieving revision 1.104
> diff -c -r1.104 pg_dump.h
> *** src/bin/pg_dump/pg_dump.h    8 Aug 2003 04:52:21 -0000    1.104
> --- src/bin/pg_dump/pg_dump.h    20 Oct 2003 07:01:13 -0000
> ***************
> *** 95,100 ****
> --- 95,108 ----
>       char       *usename;
>   } OpclassInfo;
>
> + typedef struct _convInfo
> + {
> +     char       *oid;
> +     char       *conname;
> +     NamespaceInfo *connamespace;    /* link to containing namespace */
> +     char       *usename;
> + } ConvInfo;
> +
>   typedef struct _tableInfo
>   {
>       /*
> ***************
> *** 213,218 ****
> --- 221,227 ----
>   extern AggInfo *getAggregates(int *numAggregates);
>   extern OprInfo *getOperators(int *numOperators);
>   extern OpclassInfo *getOpclasses(int *numOpclasses);
> + extern ConvInfo *getConversions(int *numConversions);
>   extern TableInfo *getTables(int *numTables);
>   extern InhInfo *getInherits(int *numInherits);
>
> ***************
> *** 230,235 ****
> --- 239,246 ----
>   extern void dumpOprs(Archive *fout, OprInfo *oprinfo, int numOperators);
>   extern void dumpOpclasses(Archive *fout,
>                 OpclassInfo *opcinfo, int numOpclasses);
> + extern void dumpConversions(Archive *fout,
> +               ConvInfo *coninfo, int numConversions);
>   extern void dumpTables(Archive *fout, TableInfo tblinfo[], int numTables,
>              const bool aclsSkip,
>              const bool schemaOnly, const bool dataOnly);

>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073