Re: [PATCHES] pg_dump -c option to drop prior to create - Mailing list pgsql-hackers

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