pg_dump extension

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);