Thread

1. drop before create in pg_dump

   Brook Milligan <brook@trillium.nmsu.edu> — 1999-01-15T21:08:46Z

   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 "
   
   

2. Re: [HACKERS] drop before create in pg_dump

   Roland Roberts <roberts@panix.com> — 1999-01-16T02:29:12Z

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