Index: src/backend/utils/adt/ruleutils.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/adt/ruleutils.c,v
retrieving revision 1.112
diff -c -r1.112 ruleutils.c
*** src/backend/utils/adt/ruleutils.c	2002/07/18 23:11:28	1.112
--- src/backend/utils/adt/ruleutils.c	2002/08/07 01:11:08
***************
*** 40,49 ****
--- 40,54 ----
  #include <unistd.h>
  #include <fcntl.h>
  
+ #include "access/genam.h"
+ #include "access/heapam.h"
  #include "catalog/heap.h"
+ #include "catalog/catname.h"
  #include "catalog/index.h"
+ #include "catalog/indexing.h"
  #include "catalog/namespace.h"
  #include "catalog/pg_cast.h"
+ #include "catalog/pg_constraint.h"
  #include "catalog/pg_index.h"
  #include "catalog/pg_opclass.h"
  #include "catalog/pg_operator.h"
***************
*** 60,65 ****
--- 65,72 ----
  #include "parser/parsetree.h"
  #include "rewrite/rewriteManip.h"
  #include "rewrite/rewriteSupport.h"
+ #include "utils/array.h"
+ #include "utils/fmgroids.h"
  #include "utils/lsyscache.h"
  
  
***************
*** 521,526 ****
--- 528,737 ----
  	PG_RETURN_TEXT_P(indexdef);
  }
  
+ /*
+  * pg_get_fkeydef
+  *
+  * Returns the definitition statement required to recreate the foreign
+  * key structure
+  */
+ Datum
+ pg_get_fkeydef(PG_FUNCTION_ARGS)
+ {
+ 	Oid				relId = PG_GETARG_OID(0);
+ 	Name			cname = PG_GETARG_NAME(1);
+ 	text		   *result;
+ 	StringInfoData	buf;
+ 	Relation		conDesc;
+ 	HeapTuple		tup;
+ 	Datum			val;
+ 	char		   *string = NULL;
+ 	SysScanDesc		rcscan;
+ 	ScanKeyData		skey[2];
+ 	int				i = 0;
+ 	int				len;
+ 	bool			isnull;
+ 	Form_pg_constraint	fkCon;
+ 
+ 	conDesc = heap_openr(ConstraintRelationName, RowExclusiveLock);
+ 
+ 	ScanKeyEntryInitialize(&skey[i++],
+ 						   0, Anum_pg_constraint_conrelid, F_OIDEQ,
+ 						   ObjectIdGetDatum(relId));
+ 
+ 	rcscan = systable_beginscan(conDesc, ConstraintRelidIndex, true,
+ 								SnapshotNow,
+ 								i, skey);
+ 
+ 	/*
+ 	 * Loop until we find the constraint with the requested name
+ 	 * or an error is thrown due to hitting the end of the list.
+ 	 */
+ 	for (;;)
+ 	{
+ 		tup = systable_getnext(rcscan);
+ 		if (!HeapTupleIsValid(tup))
+ 			elog(ERROR, "syscache lookup for foreign key %s failed on relation %u",
+ 				 NameStr(*cname), relId);
+ 
+ 		fkCon = (Form_pg_constraint) GETSTRUCT(tup);
+ 
+ 		/* Leave the loop once we've found the name we want */
+ 		if (strcmp(NameStr(fkCon->conname), NameStr(*cname)) == 0)
+ 			break;
+ 	}
+ 
+ 	/* We only deal in foreign key constraints */
+ 	if (fkCon->contype != CONSTRAINT_FOREIGN)
+ 		elog(ERROR, "pg_get_fkeydef: %s is not a foreign key constraint", NameStr(*cname));
+ 
+ 	/* Start off the statement */
+ 	initStringInfo(&buf);
+ 	appendStringInfo(&buf, "ALTER TABLE %s ADD CONSTRAINT %s FOREIGN KEY (",
+ 					 generate_relation_name(fkCon->conrelid),
+ 					 quote_identifier(NameStr(fkCon->conname)));
+ 
+ 	/* Fetch and build column list */
+ 	val = heap_getattr(tup, Anum_pg_constraint_conkey,
+ 					   RelationGetDescr(conDesc), &isnull);
+ 
+ 	if (isnull)
+ 		elog(ERROR, "get_pg_fkeydef: Null conkey array for constraint %s",
+ 			 		NameStr(*cname));
+ 	else
+ 	{
+ 		Datum	   *keys;
+ 		int			j;
+ 		int			nKeys;
+ 		deconstruct_array(DatumGetArrayTypeP(val),
+ 						  true, 2, 's',
+ 						  &keys, &nKeys);
+ 
+ 		for (j = 0; j < nKeys; j++)
+ 		{
+ 			char	   *colName;
+ 
+ 			colName	= get_attname(fkCon->conrelid,
+ 								  DatumGetInt16(keys[j]));
+ 
+ 			if (j == 0)
+ 				appendStringInfo(&buf, "%s",
+ 								 quote_identifier(colName));
+ 			else
+ 				appendStringInfo(&buf, ", %s",
+ 								 quote_identifier(colName));
+ 		}
+ 	}
+ 
+ 	appendStringInfo(&buf, ")");
+ 
+ 	/* Add Foreign relation and key attributes */
+ 	appendStringInfo(&buf, " REFERENCES %s(",
+ 					 generate_relation_name(fkCon->confrelid));
+ 
+ 	/* Fetch and build column list */
+ 	val = heap_getattr(tup, Anum_pg_constraint_confkey,
+ 					   RelationGetDescr(conDesc), &isnull);
+ 
+ 	if (isnull)
+ 		elog(ERROR, "get_pg_fkeydef: Null confkey array for constraint %s",
+ 			 		NameStr(*cname));
+ 	else
+ 	{
+ 		Datum	   *keys;
+ 		int			j;
+ 		int			nKeys;
+ 		deconstruct_array(DatumGetArrayTypeP(val),
+ 						  true, 2, 's',
+ 						  &keys, &nKeys);
+ 
+ 		for (j = 0; j < nKeys; j++)
+ 		{
+ 			char	   *colName;
+ 
+ 			colName	= get_attname(fkCon->confrelid,
+ 								  DatumGetInt16(keys[j]));
+ 
+ 			if (j == 0)
+ 				appendStringInfo(&buf, "%s",
+ 								 quote_identifier(colName));
+ 			else
+ 				appendStringInfo(&buf, ", %s",
+ 								 quote_identifier(colName));
+ 		}
+ 	}
+ 
+ 	appendStringInfo(&buf, ")");
+ 
+ 	/* Add match type */
+ 	switch (fkCon->confmatchtype)
+ 	{
+ 		case 'p':
+ 			string = " MATCH PARTIAL";
+ 			break;
+ 		case 'f':
+ 			string = " MATCH FULL";
+ 			break;
+ 		default:
+ 			string = "";
+ 			break;
+ 	}
+ 	appendStringInfo(&buf, "%s", string);
+ 
+ 	/* Add ON UPDATE and ON DELETE clause */
+ 	switch (fkCon->confupdtype)
+ 	{
+ 		case 'r':
+ 			string = "RESTRICT";
+ 			break;
+ 		case 'c':
+ 			string = "CASCADE";
+ 			break;
+ 		case 'n':
+ 			string = "SET NULL";
+ 			break;
+ 		case 'd':
+ 			string = "SET DEFAULT";
+ 			break;
+ 		case 'a':
+ 			string = "NO ACTION";
+ 			break;
+ 	}
+ 	appendStringInfo(&buf, " ON UPDATE %s",
+ 					 string);
+ 
+ 	switch (fkCon->confdeltype)
+ 	{
+ 		case 'r':
+ 			string = "RESTRICT";
+ 			break;
+ 		case 'c':
+ 			string = "CASCADE";
+ 			break;
+ 		case 'n':
+ 			string = "SET NULL";
+ 			break;
+ 		case 'd':
+ 			string = "SET DEFAULT";
+ 			break;
+ 		case 'a':
+ 			string = "NO ACTION";
+ 			break;
+ 	}
+ 	appendStringInfo(&buf, " ON DELETE %s",
+ 					 string);
+ 
+ 	/* Cleanup */
+ 	systable_endscan(rcscan);
+ 	heap_close(conDesc, RowExclusiveLock);
+ 
+ 	/* Record the results */
+ 	len = buf.len + VARHDRSZ;
+ 	result = (text *) palloc(len);
+ 	VARATT_SIZEP(result) = len;
+ 	memcpy(VARDATA(result), buf.data, buf.len);
+ 
+ 	PG_RETURN_TEXT_P(result);
+ }
  
  /* ----------
   * get_expr			- Decompile an expression tree
Index: src/bin/pg_dump/pg_dump.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/bin/pg_dump/pg_dump.c,v
retrieving revision 1.280
diff -c -r1.280 pg_dump.c
*** src/bin/pg_dump/pg_dump.c	2002/08/04 05:03:29	1.280
--- src/bin/pg_dump/pg_dump.c	2002/08/07 01:11:47
***************
*** 107,112 ****
--- 107,113 ----
  					const char *tag, const char *nspname,
  					const char *usename, const char *acl, const char *objoid);
  
+ static void dumpConstraints(Archive *fout, TableInfo *tblinfo, int numTables);
  static void dumpTriggers(Archive *fout, TableInfo *tblinfo, int numTables);
  static void dumpRules(Archive *fout, TableInfo *tblinfo, int numTables);
  static void formatStringLiteral(PQExpBuffer buf, const char *str,
***************
*** 616,621 ****
--- 617,623 ----
  	{
  		dumpTriggers(g_fout, tblinfo, numTables);
  		dumpRules(g_fout, tblinfo, numTables);
+ 		dumpConstraints(g_fout, tblinfo, numTables);
  	}
  
  	/* Now sort the output nicely */
***************
*** 5680,5686 ****
--- 5682,5789 ----
  	destroyPQExpBuffer(delqry);
  }
  
+ /*
+  * dumpConstraints
+  *
+  * Dump out constraints after all table creation statements in
+  * an alter table format.  Currently handles foreign keys only.
+  *
+  * XXX Potentially wrap in a 'SET CONSTRAINTS OFF' block so that
+  * the current table data is not processed
+  */
+ static void
+ dumpConstraints(Archive *fout, TableInfo *tblinfo, int numTables)
+ {
+ 	int			i,
+ 				j;
+ 	PQExpBuffer query = createPQExpBuffer();
+ 	PQExpBuffer delqry = createPQExpBuffer();
+ 	PGresult   *res;
+ 	int			i_condef,
+ 				i_conoid,
+ 				i_conname;
+ 	int			ntups;
+ 
+ 	/* pg_constraint was created in 7.3 */
+ 	if (g_fout->remoteVersion >= 70300)
+ 	{
  
+ 		for (i = 0; i < numTables; i++)
+ 		{
+ 			TableInfo	   *tbinfo = &tblinfo[i];
+ 
+ 			if (tbinfo->ntrig == 0 || !tbinfo->dump)
+ 				continue;
+ 
+ 			if (g_verbose)
+ 				write_msg(NULL, "dumping triggers for table %s\n",
+ 						  tbinfo->relname);
+ 
+ 			/* select table schema to ensure regproc name is qualified if needed */
+ 			selectSourceSchema(tbinfo->relnamespace->nspname);
+ 
+ 			resetPQExpBuffer(query);
+ 			appendPQExpBuffer(query,
+ 							  "SELECT pg_get_fkeydef(conrelid, conname) as condef, "
+ 							  "oid, conname "
+ 							  "from pg_catalog.pg_constraint "
+ 							  "where conrelid = '%s'::pg_catalog.oid "
+ 							  "and contype = 'f'",
+ 							  tbinfo->oid);
+ 			res = PQexec(g_conn, query->data);
+ 			if (!res ||
+ 				PQresultStatus(res) != PGRES_TUPLES_OK)
+ 			{
+ 				write_msg(NULL, "query to obtain list of foreign key definitions failed: %s", PQerrorMessage(g_conn));
+ 				exit_nicely();
+ 			}
+ 			ntups = PQntuples(res);
+ 
+ 			i_condef = PQfnumber(res, "condef");
+ 			i_conoid = PQfnumber(res, "oid");
+ 			i_conname = PQfnumber(res, "conname");
+ 	
+ 			for (j = 0; j < ntups; j++)
+ 			{
+ 				const char *conName = PQgetvalue(res, j, i_conname);
+ 				const char *conOid = PQgetvalue(res, j, i_conoid);
+ 				const char *conDef = PQgetvalue(res, j, i_condef);
+ 
+ 				resetPQExpBuffer(query);
+ 				/*
+ 				 * Creation query is built by pg_get_fkeydef.
+ 				 * We don't need to DROP Foreign keys as the
+ 				 * table drop will take care of that.
+ 				 */
+ 				appendPQExpBuffer(query, "%s;",
+ 								  conDef);
+ 
+ 				ArchiveEntry(fout, conOid,
+ 							 conName,
+ 							 tbinfo->relnamespace->nspname,
+ 							 tbinfo->usename,
+ 							 "CONSTRAINT", NULL,
+ 							 query->data, delqry->data,
+ 							 NULL, NULL, NULL);
+ 
+ 				resetPQExpBuffer(query);
+ 				appendPQExpBuffer(query, "CONSTRAINT %s ",
+ 								  fmtId(conName, force_quotes));
+ 				appendPQExpBuffer(query, "ON %s",
+ 								  fmtId(tbinfo->relname, force_quotes));
+ 
+ 				dumpComment(fout, query->data,
+ 							tbinfo->relnamespace->nspname, tbinfo->usename,
+ 							conOid, "pg_constraint", 0, NULL);
+ 			}
+ 
+ 			PQclear(res);
+ 		}
+ 	}
+ 	destroyPQExpBuffer(query);
+ 	destroyPQExpBuffer(delqry);
+ }
+ 
  static void
  dumpTriggers(Archive *fout, TableInfo *tblinfo, int numTables)
  {
***************
*** 5728,5734 ****
  							  "tgconstrrelid, tginitdeferred, oid, "
  							  "tgconstrrelid::pg_catalog.regclass as tgconstrrelname "
  							  "from pg_catalog.pg_trigger "
! 							  "where tgrelid = '%s'::pg_catalog.oid",
  							  tbinfo->oid);
  		}
  		else
--- 5831,5838 ----
  							  "tgconstrrelid, tginitdeferred, oid, "
  							  "tgconstrrelid::pg_catalog.regclass as tgconstrrelname "
  							  "from pg_catalog.pg_trigger "
! 							  "where tgrelid = '%s'::pg_catalog.oid "
! 							  "and tgisconstraint is false",
  							  tbinfo->oid);
  		}
  		else
***************
*** 5752,5758 ****
  			exit_nicely();
  		}
  		ntups = PQntuples(res);
! 		if (ntups != tbinfo->ntrig)
  		{
  			write_msg(NULL, "expected %d triggers on table \"%s\" but found %d\n",
  					  tbinfo->ntrig, tbinfo->relname, ntups);
--- 5856,5866 ----
  			exit_nicely();
  		}
  		ntups = PQntuples(res);
! 		/*
! 		 * We may have less triggers than recorded due to constraint triggers
! 		 * which are dumped by dumpConstraints
! 		 */
! 		if (ntups > tbinfo->ntrig)
  		{
  			write_msg(NULL, "expected %d triggers on table \"%s\" but found %d\n",
  					  tbinfo->ntrig, tbinfo->relname, ntups);
Index: src/bin/psql/describe.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/bin/psql/describe.c,v
retrieving revision 1.57
diff -c -r1.57 describe.c
*** src/bin/psql/describe.c	2002/08/02 18:15:08	1.57
--- src/bin/psql/describe.c	2002/08/07 01:11:52
***************
*** 396,402 ****
  
  			 ") AS tt,\n"
  			 "pg_description d\n"
! 			 "WHERE tt.oid = d.objoid and tt.tableoid = d.classoid and d.objsubid = 0\n",
  
  			 _("Name"), _("Object"), _("Description"),
  			 _("aggregate"), _("function"), _("operator"),
--- 396,403 ----
  
  			 ") AS tt,\n"
  			 "pg_description d\n"
! 			 "WHERE t.tgisconstraint IS FALSE\n"
! 			 "AND tt.oid = d.objoid and tt.tableoid = d.classoid and d.objsubid = 0\n",
  
  			 _("Name"), _("Object"), _("Description"),
  			 _("aggregate"), _("function"), _("operator"),
***************
*** 745,753 ****
  		PGresult   *result1 = NULL,
  				   *result2 = NULL,
  				   *result3 = NULL,
! 				   *result4 = NULL;
! 		int			index_count = 0,
! 					check_count = 0,
  					rule_count = 0,
  					trigger_count = 0;
  		int			count_footers = 0;
--- 746,756 ----
  		PGresult   *result1 = NULL,
  				   *result2 = NULL,
  				   *result3 = NULL,
! 				   *result4 = NULL,
! 				   *result5 = NULL;
! 		int			check_count = 0,
! 					index_count = 0,
! 					foreignkey_count = 0,
  					rule_count = 0,
  					trigger_count = 0;
  		int			count_footers = 0;
***************
*** 801,813 ****
  				rule_count = PQntuples(result3);
  		}
  
! 		/* count triggers */
  		if (tableinfo.triggers)
  		{
  			printfPQExpBuffer(&buf,
  					"SELECT t.tgname\n"
  					"FROM pg_trigger t, pg_class c\n"
! 					"WHERE c.relname='%s' AND c.oid = t.tgrelid",
  					name);
  			result4 = PSQLexec(buf.data);
  			if (!result4)
--- 804,817 ----
  				rule_count = PQntuples(result3);
  		}
  
! 		/* count triggers but ignore foreign key triggers */
  		if (tableinfo.triggers)
  		{
  			printfPQExpBuffer(&buf,
  					"SELECT t.tgname\n"
  					"FROM pg_trigger t, pg_class c\n"
! 					"WHERE t.tgisconstraint IS FALSE\n"
! 					"AND c.relname='%s' AND c.oid = t.tgrelid",
  					name);
  			result4 = PSQLexec(buf.data);
  			if (!result4)
***************
*** 815,820 ****
--- 819,838 ----
  			else
  				trigger_count = PQntuples(result4);
  		}
+  		/* Fetch the Foreign Key information */
+  		printfPQExpBuffer(&buf,
+  					"SELECT conname,\n"
+  					"SUBSTR(pg_get_fkeydef(c.oid, conname),\n"
+ 					" POSITION('FOREIGN KEY' in pg_get_fkeydef(c.oid, conname)\n"
+ 					" )) as condef\n"
+  					"FROM pg_class as c JOIN pg_constraint ON (c.oid = conrelid)\n"
+  					"WHERE c.relname = '%s' AND contype = 'f'",
+  					name);
+  		result5 = PSQLexec(buf.data);
+  		if (!result5)
+  			goto error_return;
+  		else
+  			foreignkey_count = PQntuples(result5);
  
  		footers = xmalloc((index_count + check_count + rule_count + trigger_count + 1)
  						  * sizeof(*footers));
***************
*** 862,867 ****
--- 880,903 ----
  								  (int) strlen(s), "",
  								  PQgetvalue(result2, i, 1),
  								  PQgetvalue(result2, i, 0));
+ 			footers[count_footers++] = xstrdup(buf.data);
+ 		}
+ 
+ 		/* print foreign key constraints */
+ 		for (i = 0; i < foreignkey_count; i++)
+ 		{
+ 			char	   *s = _("Foreign Key constraints");
+ 
+ 			if (i == 0)
+ 				printfPQExpBuffer(&buf, _("%s: %s %s"),
+ 								  s,
+ 								  PQgetvalue(result5, i, 0),
+ 								  PQgetvalue(result5, i, 1));
+ 			else
+ 				printfPQExpBuffer(&buf, _("%*s  %s %s"),
+ 								  (int) strlen(s), "",
+ 								  PQgetvalue(result5, i, 0),
+ 								  PQgetvalue(result5, i, 1));
  			footers[count_footers++] = xstrdup(buf.data);
  		}
  
Index: src/include/catalog/pg_proc.h
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/include/catalog/pg_proc.h,v
retrieving revision 1.252
diff -c -r1.252 pg_proc.h
*** src/include/catalog/pg_proc.h	2002/08/06 05:40:45	1.252
--- src/include/catalog/pg_proc.h	2002/08/07 01:12:37
***************
*** 2164,2169 ****
--- 2164,2171 ----
  DESCR("greater-than-or-equal");
  
  /* System-view support functions */
+ DATA(insert OID = 1387 (  pg_get_fkeydef       PGNSP PGUID 12 f f t f s 2 25 "26 19" pg_get_fkeydef - _null_ ));
+ DESCR("alter table foreign key creation statement");
  DATA(insert OID = 1573 (  pg_get_ruledef	   PGNSP PGUID 12 f f t f s 1 25 "26"  pg_get_ruledef - _null_ ));
  DESCR("source text of a rule");
  DATA(insert OID = 1640 (  pg_get_viewdef	   PGNSP PGUID 12 f f t f s 1 25 "25"  pg_get_viewdef_name - _null_ ));
Index: src/include/utils/builtins.h
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/include/utils/builtins.h,v
retrieving revision 1.189
diff -c -r1.189 builtins.h
*** src/include/utils/builtins.h	2002/08/06 14:11:05	1.189
--- src/include/utils/builtins.h	2002/08/07 01:12:45
***************
*** 347,352 ****
--- 347,353 ----
  extern char *format_operator(Oid operator_oid);
  
  /* ruleutils.c */
+ extern Datum pg_get_fkeydef(PG_FUNCTION_ARGS);
  extern Datum pg_get_ruledef(PG_FUNCTION_ARGS);
  extern Datum pg_get_viewdef(PG_FUNCTION_ARGS);
  extern Datum pg_get_viewdef_name(PG_FUNCTION_ARGS);