I've hacked up pg_dump so that it generates CONSTRAINT and CHECK

syntax that can be read back in with psql.  I did this by adding
a
 "-c" switch that controls moving the CONTSTRAINT statements inside
the CREATE TABLE statements and adding () around the CHECK arguments.
Here's diffs against the 6.3.2 version of pg_dump.c.

ccb

1 files changed, 30 insertions, 5 deletions

diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index b57c8bf85b1..613b2a5b88a 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -21,7 +21,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/bin/pg_dump/pg_dump.c,v 1.72 1998/06/15 19:30:01 momjian Exp $
+ *	  $Header: /cvsroot/pgsql/src/bin/pg_dump/pg_dump.c,v 1.73 1998/06/16 06:52:15 momjian Exp $
  *
  * Modifications - 6/10/96 - dave@bensoft.com - version 1.13.dhb
  *
@@ -110,6 +110,7 @@ int			dumpData;			/* dump data using proper insert strings */
 int			attrNames;			/* put attr names into insert strings */
 int			schemaOnly;
 int			dataOnly;
+int                     compatConstraint;
 
 char		g_opaque_type[10];	/* name for the opaque type */
 
@@ -126,6 +127,8 @@ usage(const char *progname)
 	fprintf(stderr,
 			"\t -a          \t\t dump out only the data, no schema\n");
 	fprintf(stderr,
+		        "\t -c          \t\t generate pgsql-compatible CONSTRAINT syntax\n");
+	fprintf(stderr,
 			"\t -d          \t\t dump data as proper insert strings\n");
 	fprintf(stderr,
 	  "\t -D          \t\t dump data as inserts with attribute names\n");
@@ -550,17 +553,21 @@ main(int argc, char **argv)
 	g_comment_end[0] = '\0';
 	strcpy(g_opaque_type, "opaque");
 
-	dataOnly = schemaOnly = dumpData = attrNames = 0;
+	compatConstraint = dataOnly = schemaOnly = dumpData = attrNames = 0;
 
 	progname = *argv;
 
-	while ((c = getopt(argc, argv, "adDf:h:op:st:vzu")) != EOF)
+	while ((c = getopt(argc, argv, "acdDf:h:op:st:vzu")) != EOF)
 	{
 		switch (c)
 		{
 			case 'a':			/* Dump data only */
 				dataOnly = 1;
 				break;
+		        case 'c':                       /* generate constraint syntax that
+							    can be read back into postgreSQL */
+			        compatConstraint = 1;
+				break;
 			case 'd':			/* dump data as proper insert strings */
 				dumpData = 1;
 				break;
@@ -1491,7 +1498,12 @@ getTables(int *numTables, FuncInfo *finfo, int numFuncs)
 				query[0] = 0;
 				if (name[0] != '$')
 					sprintf(query, "CONSTRAINT %s ", name);
-				sprintf(query, "%sCHECK %s", query, expr);
+				if( compatConstraint ) {
+				  sprintf(query, "%sCHECK (%s)", query, expr);
+				}
+				else {
+				  sprintf(query, "%sCHECK %s", query, expr);
+				}
 				tblinfo[i].check_expr[i2] = strdup(query);
 			}
 			PQclear(res2);
@@ -2509,6 +2521,17 @@ dumpTables(FILE *fout, TableInfo *tblinfo, int numTables,
 				}
 			}
 
+			if( compatConstraint ) {
+				/* put the CONSTRAINTS inside the table def */
+				for (k = 0; k < tblinfo[i].ncheck; k++)
+				{
+					sprintf(q, "%s%s %s",
+						q,
+						(actual_atts + k > 0) ? ", " : "",
+						tblinfo[i].check_expr[k]);
+				}
+			}
+
 			strcat(q, ")");
 
 			if (numParents > 0)
@@ -2524,8 +2547,9 @@ dumpTables(FILE *fout, TableInfo *tblinfo, int numTables,
 				strcat(q, ")");
 			}
 
-			if (tblinfo[i].ncheck > 0)
+			if( !compatConstraint )
 			{
+				/* put the CONSTRAINT defs outside the table def */
 				for (k = 0; k < tblinfo[i].ncheck; k++)
 				{
 					sprintf(q, "%s%s %s",
@@ -2534,6 +2558,7 @@ dumpTables(FILE *fout, TableInfo *tblinfo, int numTables,
 							tblinfo[i].check_expr[k]);
 				}
 			}
+
 			strcat(q, ";\n");
 			fputs(q, fout);
 			if (acls)