Thread: pg_dump -c option to drop prior to create
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 "
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,
Pennsylvania19026