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