drop before create in pg_dump - Mailing list pgsql-hackers

pg_dump won't drop stuff before trying to create it; this makes
dropping a single table (say) and recreating it difficult to automate
since it is subject to error if all the stuff doesn't get dropped
properly.  The following patch causes pg_dump to emit DROP ...
statements prior to emitting CREATE ... statements.

I don't have experience with things like aggregates, languages, types,
functions, ... so I'm not quite sure I got the DROP ... syntax correct
in all instances.  I tried to follow the man pages and the fields put
into the CREATE ... statements, so it should be ok.  Help testing this
would be great.  (Do our regression tests test pg_dump?)

At this point, I don't understand how the CREATE TRIGGER statements
are constructed; they don't follow the same style as anything else in
pg_dump.  As a result, I'm not clear on how to implement the DROP
TRIGGER statements.  Help appreciated here.

Finally, this patch should go in after the earlier one I submitted to
create SERIAL sequencies.

Cheers,
Brook

===========================================================================

--- bin/pg_dump/pg_dump.c.orig    Fri Jan 15 12:26:34 1999
+++ bin/pg_dump/pg_dump.c    Fri Jan 15 13:02:34 1999
@@ -1630,6 +1630,13 @@                    exit_nicely(g_conn);                }                tgfunc =
finfo[findx].proname;
+
+#if 0                
+                /* XXX - how to emit this DROP TRIGGER? */
+                sprintf(query, "DROP TRIGGER %s;\n", fmtId(PQgetvalue(res2, i2, i_tgname), 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 +2033,9 @@        becomeUser(fout, tinfo[i].usename);
+        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 +2132,8 @@        lanname = checkForQuote(PQgetvalue(res, i, i_lanname));        lancompiler =
checkForQuote(PQgetvalue(res,i, i_lancompiler));
 
+        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 +2249,20 @@        PQclear(res);    }
+    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 +2373,11 @@        becomeUser(fout, oprinfo[i].usename);
+        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 +2473,10 @@        becomeUser(fout, agginfo[i].usename);
+        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 +2676,9 @@            becomeUser(fout, tblinfo[i].usename);
+            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 +2895,10 @@            strcpy(id1, fmtId(indinfo[i].indexrelname, force_quotes));            strcpy(id2,
fmtId(indinfo[i].indrelname,force_quotes));
 
+
+            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 +3158,9 @@    called = *t;    PQclear(res);
+
+    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 "