pg_dump extension - Mailing list pgsql-general

The following patch allows pg_dump to dump out data about
user-defined operator classes

Wolfgang Hamann

--- pg_dump.c.orig    Sat Jul 17 09:01:44 1999
+++ pg_dump.c    Sat Jul 17 13:34:03 1999
@@ -2371,6 +2371,107 @@
 }

 /*
+ * dumpOpclasses
+ *      writes out to fout the queries to recreate all the user-defined operator classes
+ *
+ */
+void
+dumpOpclasses(FILE *fout)
+{    int n, ntuples;
+
+    char        q[MAX_QUERY_SIZE];
+    PGresult   *res;
+
+    sprintf(q,
+        "SELECT DISTINCT opcname from pg_opclass where oid >%u",
+        g_last_builtin_oid);
+    res = PQexec(g_conn, q);
+    if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
+    {
+        fprintf(stderr, "dumpOpclass(): SELECT (opcname) failed.  Explanation from backend: '%s'.\n",
PQerrorMessage(g_conn));
+        exit_nicely(g_conn);
+    }
+    ntuples = PQntuples(res);
+    for(n = 0 ; n < ntuples ; n++)
+    {
+        sprintf(q, "INSERT INTO pg_opclass (opcname) VALUES ('%s');\n",
+         PQgetvalue(res, n, PQfnumber(res, "opcname")));
+        fputs(q, fout);
+    }
+    PQclear(res);
+
+    sprintf(q,
+        "SELECT DISTINCT pg_am.amname, cl.opcname, o.oprname, t1.typname as ltypname, t2.typname as rtypname,"
+        " amopstrategy, amopselect, amopnpages"
+        " FROM pg_amop, pg_operator o, pg_opclass cl, pg_type t1, pg_type t2"
+        " WHERE (amopid > %u OR amopclaid > %u OR amopopr > %u) AND"
+        " pg_am.oid = amopid AND cl.oid = amopclaid AND"
+        " o.oid = amopopr AND o.oprleft = t1.oid AND o.oprright = t2.oid",
+        g_last_builtin_oid, g_last_builtin_oid, g_last_builtin_oid);
+    res = PQexec(g_conn, q);
+    if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
+    {
+        fprintf(stderr, "dumpOpclass(): SELECT (amname, opcname, oprname, ..) failed.  Explanation from backend:
'%s'.\n",PQerrorMessage(g_conn)); 
+        exit_nicely(g_conn);
+    }
+    ntuples = PQntuples(res);
+    for(n = 0 ; n < ntuples ; n++)
+    {
+        sprintf(q,
+            "INSERT INTO pg_amop(amopid, amopclaid, amopopr, amopstrategy,"
+            " amopselect, amopnpages)\n"
+            " SELECT am.oid, cl.oid, o.oid, %s, '%s'::regproc, "
+            "'%s'::regproc\n"
+            " FROM pg_am am, pg_opclass cl, pg_operator o, pg_type t1, pg_type t2\n"
+            " WHERE am.amname = '%s'"
+            " AND cl.opcname = '%s'"
+            " AND o.oprname = '%s'\n"
+            " AND o.oprleft = t1.oid AND o.oprright = t2.oid\n"
+            " AND t1.typname = '%s'"
+            " AND t2.typname = '%s';\n",
+            PQgetvalue(res, n, PQfnumber(res, "amopstrategy")),
+            PQgetvalue(res, n, PQfnumber(res, "amopselect")),
+            PQgetvalue(res, n, PQfnumber(res, "amopnpages")),
+            PQgetvalue(res, n, PQfnumber(res, "amname")),
+            PQgetvalue(res, n, PQfnumber(res, "opcname")),
+            PQgetvalue(res, n, PQfnumber(res, "oprname")),
+            PQgetvalue(res, n, PQfnumber(res, "ltypname")),
+            PQgetvalue(res, n, PQfnumber(res, "rtypname")));
+        fputs(q, fout);
+    }
+    PQclear(res);
+
+    sprintf(q,
+        "SELECT DISTINCT pg_am.amname, cl.opcname, p.proname, amprocnum"
+        " FROM pg_amproc, pg_opclass cl, pg_proc p"
+        " WHERE (amid > %u or amopclaid > %u)"
+        " AND pg_am.oid = amid AND cl.oid = amopclaid AND p.oid = amproc",
+        g_last_builtin_oid, g_last_builtin_oid);
+    res = PQexec(g_conn, q);
+    if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
+    {
+        fprintf(stderr, "dumpOpclass(): SELECT (amname, opcname, proname, ..) failed.  Explanation from backend:
'%s'.\n",PQerrorMessage(g_conn)); 
+        exit_nicely(g_conn);
+    }
+    ntuples = PQntuples(res);
+    for(n = 0 ; n < ntuples ; n++)
+    {
+        sprintf(q,
+            "INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum)\n"
+            " SELECT pg_am.oid, pg_opclass.oid, pg_proc.oid, %s\n"
+            " WHERE pg_am.amname = '%s' AND pg_opclass.opcname = '%s'"
+            " AND pg_proc.proname = '%s';\n",
+            PQgetvalue(res, n, PQfnumber(res, "amprocnum")),
+            PQgetvalue(res, n, PQfnumber(res, "amname")),
+            PQgetvalue(res, n, PQfnumber(res, "opcname")),
+            PQgetvalue(res, n, PQfnumber(res, "proname")));
+        fputs(q, fout);
+    }
+    PQclear(res);
+
+}
+
+/*
  * dumpAggs
  *      writes out to fout the queries to create all the user-defined aggregates
  *
--- common.c.orig    Sat Jul 17 09:13:29 1999
+++ common.c    Sat Jul 17 12:33:40 1999
@@ -335,6 +335,14 @@
                     g_comment_start, g_comment_end);
         dumpOprs(fout, oprinfo, numOperators, tinfo, numTypes);
     }
+// opclass
+    if (!tablename && fout)
+    {
+        if (g_verbose)
+            fprintf(stderr, "%s dumping out user-defined operator classes %s\n",
+                    g_comment_start, g_comment_end);
+        dumpOpclasses(fout);
+    }

     *numTablesPtr = numTables;
     clearAggInfo(agginfo, numAggregates);