pg_dump -c option to drop prior to create

Applied.

Here is a new version of my patch for allowing pg_dump to DROP schema
elements prior to CREATEing new ones. It is under control of the -c
command line option (with the default being status quo).

The DROP TRIGGER portion still needs implementation. Anyone able to
help clarify what exactly the CREATE TRIGGER portion does so I can fix
this?

Again, I have tried this with tables/indexes/sequences, but do not
have other schema elements in my database. As a result, I am not 100%
convinced that I got the syntax correct in all cases (but think I did,
nonetheless). If anyone can check the other cases, I'd appreciate it.

Cheers,
Brook

===========================================================================
--- bin/pgdump/pg_dump.c.orig	Fri Jan 15 12:26:34 1999
+++ bin/pgdump/pg_dump.c	Mon Jan 18 09:27:35 1999
@@ -113,6 +113,7 @@
int		schemaOnly;
int		dataOnly;
int		aclsOption;
+bool		drop_schema;

char g_opaque_type[10]; /* name for the opaque type */

@@ -129,6 +130,8 @@
fprintf(stderr,
"\t -a          \t\t dump out only the data, no schema\n");
fprintf(stderr,
+			"\t -c          \t\t clean (i.e., drop) schema prior to create\n");
+	fprintf(stderr,
"\t -d          \t\t dump data as proper insert strings\n");
fprintf(stderr,
"\t -D          \t\t dump data as inserts"
@@ -552,6 +555,7 @@

g_verbose = false;
force_quotes = true;
+ drop_schema = false;

strcpy(g_comment_start, "-- ");
g_comment_end[0] = '\0';
@@ -561,13 +565,16 @@

progname = *argv;

-	while ((c = getopt(argc, argv, "adDf:h:nNop:st:vzu")) != EOF)
+	while ((c = getopt(argc, argv, "acdDf:h:nNop:st:vzu")) != EOF)
{
switch (c)
{
case 'a':			/* Dump data only */
dataOnly = 1;
break;
+			case 'c':			/* clean (i.e., drop) schema prior to create */
+				drop_schema = true;
+				break;
case 'd':			/* dump data as proper insert strings */
dumpData = 1;
break;
@@ -1630,6 +1637,18 @@
exit_nicely(g_conn);
}
tgfunc = finfo[findx].proname;
+
+#if 0				
+				/* XXX - how to emit this DROP TRIGGER? */
+				if (drop_schema)
+				  {
+				    sprintf(query, "DROP TRIGGER %s ON %s;\n",
+					    fmtId(PQgetvalue(res2, i2, i_tgname), force_quotes),
+					    fmtId(tblinfo[i].relname, force_quotes));
+				    fputs(query, fout);
+				  }
+#endif
+
sprintf(query, "CREATE TRIGGER %s ", fmtId(PQgetvalue(res2, i2, i_tgname), force_quotes));
/* Trigger type */
findx = 0;
@@ -2026,6 +2045,12 @@

becomeUser(fout, tinfo[i].usename);

+		if (drop_schema)
+		  {
+		    sprintf(q, "DROP TYPE %s;\n", fmtId(tinfo[i].typname, force_quotes));
+		    fputs(q, fout);
+		  }
+
sprintf(q,
"CREATE TYPE %s "
"( internallength = %s, externallength = %s, input = %s, "
@@ -2122,6 +2147,9 @@
lanname = checkForQuote(PQgetvalue(res, i, i_lanname));
lancompiler = checkForQuote(PQgetvalue(res, i, i_lancompiler));

+		if (drop_schema)
+		  fprintf(fout, "DROP PROCEDURAL LANGUAGE '%s';\n", lanname);
+
fprintf(fout, "CREATE %sPROCEDURAL LANGUAGE '%s' "
"HANDLER %s LANCOMPILER '%s';\n",
(PQgetvalue(res, i, i_lanpltrusted)[0] == 't') ? "TRUSTED " : "",
@@ -2237,6 +2265,23 @@
PQclear(res);
}

+	if (drop_schema)
+	  {
+	    sprintf(q, "DROP FUNCTION %s (", fmtId(finfo[i].proname, force_quotes));
+	    for (j = 0; j < finfo[i].nargs; j++)
+	      {
+		char	   *typname;
+		
+		typname = findTypeByOid(tinfo, numTypes, finfo[i].argtypes[j]);
+		sprintf(q, "%s%s%s",
+			q,
+			(j > 0) ? "," : "",
+			fmtId(typname, false));
+	      }
+	    sprintf (q, "%s);\n", q);
+	    fputs(q, fout);
+	  }
+
sprintf(q, "CREATE FUNCTION %s (", fmtId(finfo[i].proname, force_quotes));
for (j = 0; j < finfo[i].nargs; j++)
{
@@ -2347,6 +2392,14 @@

becomeUser(fout, oprinfo[i].usename);

+		if (drop_schema)
+		  {
+		    sprintf(q, "DROP OPERATOR %s (%s, %s);\n", oprinfo[i].oprname, 
+			    fmtId(findTypeByOid(tinfo, numTypes, oprinfo[i].oprleft), false),
+			    fmtId(findTypeByOid(tinfo, numTypes, oprinfo[i].oprright), false));
+		    fputs(q, fout);
+		  }
+
sprintf(q,
"CREATE OPERATOR %s "
"(PROCEDURE = %s %s %s %s %s %s %s %s %s);\n ",
@@ -2442,6 +2495,13 @@

becomeUser(fout, agginfo[i].usename);

+		if (drop_schema)
+		  {
+		    sprintf(q, "DROP AGGREGATE %s %s;\n", agginfo[i].aggname,
+			    fmtId(findTypeByOid(tinfo, numTypes, agginfo[i].aggbasetype), false));
+		    fputs(q, fout);
+		  }
+
sprintf(q, "CREATE AGGREGATE %s ( %s %s%s %s%s %s );\n",
agginfo[i].aggname,
basetype,
@@ -2641,6 +2701,12 @@

becomeUser(fout, tblinfo[i].usename);

+			if (drop_schema)
+			  {
+			    sprintf(q, "DROP TABLE %s;\n", fmtId(tblinfo[i].relname, force_quotes));
+			    fputs(q, fout);
+			  }
+
sprintf(q, "CREATE TABLE %s (\n\t", fmtId(tblinfo[i].relname, force_quotes));
actual_atts = 0;
for (j = 0; j < tblinfo[i].numatts; j++)
@@ -2857,6 +2923,13 @@

strcpy(id1, fmtId(indinfo[i].indexrelname, force_quotes));
strcpy(id2, fmtId(indinfo[i].indrelname, force_quotes));
+
+			if (drop_schema)
+			  {
+			    sprintf(q, "DROP INDEX %s;\n", id1);
+			    fputs(q, fout);
+			  }
+
fprintf(fout, "CREATE %s INDEX %s on %s using %s (",
(strcmp(indinfo[i].indisunique, "t") == 0) ? "UNIQUE" : "",
id1,
@@ -3116,6 +3189,12 @@
called = *t;

PQclear(res);
+
+	if (drop_schema)
+	  {
+	    sprintf(query, "DROP SEQUENCE %s;\n", fmtId(tbinfo.relname, force_quotes));
+	    fputs(query, fout);
+	  }

sprintf(query,
"CREATE SEQUENCE %s start %d increment %d maxvalue %d "

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026