pg_dump -c option to drop prior to create - Mailing list pgsql-hackers

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 "