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 "

Fri, 15 Jan 1999 14:08:46 -0700 (MST)
Brook Milligan <brook@trillium.NMSU.Edu>
drop before create in pg_dump
Brook Milligan <brook@trillium.NMSU.Edu> writes:
> 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 think that ought to be driven by a switch to pg_dump ...
much of the time, I would *want* pg_dump's script to fail
if there's already an existing item of the given name.

I see your concern, I just don't think it's the only scenario.

			regards, tom lane

Fri, 15 Jan 1999 18:44:32 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] drop before create in pg_dump 
-----BEGIN PGP SIGNED MESSAGE-----

>>>>> "bm" == Brook Milligan <brook@trillium.NMSU.Edu> writes:

    bm> The following patch causes pg_dump to emit DROP ... statements
    bm> prior to emitting CREATE ... statements.

I'm not sure I want pg_dump output to automatically drop tables.  I'd
certainly prefer having to ask for this behavior....

roland
- -- 
		       PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD                  Custom Software Solutions
roberts@panix.com                           101 West 15th St #4NN
rbroberts@acm.org                              New York, NY 10011

-----BEGIN PGP SIGNATURE-----
Version: 2.6.2
Comment: Processed by Mailcrypt 3.4, an Emacs/PGP interface

iQCVAwUBNp/5duoW38lmvDvNAQGJ4QP9EmXQWyUtgJi+5EkykZVU5IW9IMj/YcFa
yGXF6eGP1SfLsW7rQPHgIljPvbT9B6WobYpGrluqA00XoW0/Wsm4TmjXuGQxRiI0
VxveN127wFsKrA0oJb8g9IfziyR2oeUba9oPNK3Yg2sYAXvd8/schYf6Ub2KUH5Q
FS7ylJV1awY=
=y2FK
-----END PGP SIGNATURE-----

15 Jan 1999 21:29:12 -0500
Roland Roberts <roberts@panix.com>
Re: [HACKERS] drop before create in pg_dump
> Brook Milligan <brook@trillium.NMSU.Edu> writes:
> > 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 think that ought to be driven by a switch to pg_dump ...
> much of the time, I would *want* pg_dump's script to fail
> if there's already an existing item of the given name.
> 
> I see your concern, I just don't think it's the only scenario.

I agree.  I don't like it removing stuff before creating it.  pg_dump
already does a lot.  But I do agree it is difficult to automate
destroy/create.


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

Fri, 15 Jan 1999 22:50:51 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] drop before create in pg_dump
   Brook Milligan <brook@trillium.NMSU.Edu> writes:
   > 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 think that ought to be driven by a switch to pg_dump ...
   much of the time, I would *want* pg_dump's script to fail
   if there's already an existing item of the given name.

A switch is fine.  Is there any concensus as to what it should be,
since the two that come to mind (-D and -d) are both used?

Cheers,
Brook

Sun, 17 Jan 1999 15:11:47 -0700 (MST)
Brook Milligan <brook@trillium.NMSU.Edu>
Re: [HACKERS] drop before create in pg_dump
>    Brook Milligan <brook@trillium.NMSU.Edu> writes:
>    > 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 think that ought to be driven by a switch to pg_dump ...
>    much of the time, I would *want* pg_dump's script to fail
>    if there's already an existing item of the given name.
> 
> A switch is fine.  Is there any concensus as to what it should be,
> since the two that come to mind (-D and -d) are both used?

-c for clean.

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

Sun, 17 Jan 1999 23:51:14 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] drop before create in pg_dump