? GNUmakefile
? alpha-patch.txt
? config.cache
? config.log
? config.status
? configure.out
? domaintest.sql
? null.txt
? regression.txt
? contrib/tree
? contrib/tree.tar.gz
? contrib/intagg/int_aggregate.sql
? src/GNUmakefile
? src/Makefile.global
? src/backend/postgres
? src/backend/catalog/postgres.bki
? src/backend/catalog/postgres.description
? src/bin/initdb/initdb
? src/bin/initlocation/initlocation
? src/bin/ipcclean/ipcclean
? src/bin/pg_config/pg_config
? src/bin/pg_ctl/pg_ctl
? src/bin/pg_dump/pg_dump
? src/bin/pg_dump/pg_dumpall
? src/bin/pg_dump/pg_restore
? src/bin/pg_id/pg_id
? src/bin/pg_passwd/pg_passwd
? src/bin/psql/psql
? src/bin/scripts/createlang
? src/include/pg_config.h
? src/include/stamp-h
? src/interfaces/ecpg/lib/libecpg.so.3
? src/interfaces/ecpg/preproc/ecpg
? src/interfaces/libpgeasy/libpgeasy.so.2
? src/interfaces/libpq/libpq.so.2
? src/pl/plpgsql/src/libplpgsql.so.1
? src/test/regress/log
? src/test/regress/pg_regress
? src/test/regress/postgres.core
? src/test/regress/results
? src/test/regress/tmp_check
? src/test/regress/expected/bak.out
? src/test/regress/expected/constraints.out
? src/test/regress/expected/copy.out
? src/test/regress/expected/create_function_1.out
? src/test/regress/expected/create_function_2.out
? src/test/regress/expected/misc.out
? src/test/regress/sql/constraints.sql
? src/test/regress/sql/copy.sql
? src/test/regress/sql/create_function_1.sql
? src/test/regress/sql/create_function_2.sql
? src/test/regress/sql/misc.sql
Index: doc/TODO
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/TODO,v
retrieving revision 1.775
diff -c -r1.775 TODO
*** doc/TODO	2002/03/25 20:56:08	1.775
--- doc/TODO	2002/03/27 06:12:03
***************
*** 185,192 ****
  	o Add ALTER TABLE DROP COLUMN feature [drop] (Bruce)
  	o Add ALTER FUNCTION
  	o Add ALTER TABLE DROP non-CHECK CONSTRAINT
! 	o ALTER TABLE ADD PRIMARY KEY (Christopher Kings-Lynne)
! 	o ALTER TABLE ADD UNIQUE (Christopher Kings-Lynne)
  	o ALTER TABLE ADD COLUMN column SERIAL doesn't create sequence
  	o ALTER TABLE ADD COLUMN column SET DEFAULT should fill existing
  	  rows with DEFAULT value
--- 185,193 ----
  	o Add ALTER TABLE DROP COLUMN feature [drop] (Bruce)
  	o Add ALTER FUNCTION
  	o Add ALTER TABLE DROP non-CHECK CONSTRAINT
! 	o -ALTER TABLE ADD PRIMARY KEY (Tom)
! 	o -ALTER TABLE ADD UNIQUE (Tom)
! 	o -ALTER TABLE ALTER COLUMN SET/DROP NOT NULL (Christopher Kings-Lynne)
  	o ALTER TABLE ADD COLUMN column SERIAL doesn't create sequence
  	o ALTER TABLE ADD COLUMN column SET DEFAULT should fill existing
  	  rows with DEFAULT value
Index: doc/src/sgml/ref/alter_table.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/alter_table.sgml,v
retrieving revision 1.40
diff -c -r1.40 alter_table.sgml
*** doc/src/sgml/ref/alter_table.sgml	2002/03/06 20:42:38	1.40
--- doc/src/sgml/ref/alter_table.sgml	2002/03/27 06:12:03
***************
*** 29,34 ****
--- 29,36 ----
      ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> { SET DEFAULT <replaceable
      class="PARAMETER">value</replaceable> | DROP DEFAULT }
  ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ * ]
+     ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> { SET | DROP } NOT NULL
+ ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ * ]
      ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET STATISTICS <replaceable class="PARAMETER">integer</replaceable>
  ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ * ]
      ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET STORAGE {PLAIN | EXTERNAL | EXTENDED | MAIN}
***************
*** 168,173 ****
--- 170,178 ----
     allow you to set or remove the default for the column. Note that defaults
     only apply to subsequent <command>INSERT</command> commands; they do not
     cause rows already in the table to change.
+    The <literal>ALTER COLUMN SET/DROP NOT NULL</literal> forms allow you to
+    change whether a column is marked to allow NULL values or to reject NULL
+    values.
     The <literal>ALTER COLUMN SET STATISTICS</literal> form allows you to
     set the statistics-gathering target for subsequent
     <xref linkend="sql-analyze" endterm="sql-analyze-title"> operations.
***************
*** 276,281 ****
--- 281,297 ----
     To rename an existing table:
     <programlisting>
  ALTER TABLE distributors RENAME TO suppliers;
+    </programlisting>
+   </para>
+ 
+   <para>
+    To add a NOT NULL constraint to a column:
+    <programlisting>
+ ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;
+    </programlisting>
+    To remove a NOT NULL constraint from a column:
+    <programlisting>
+ ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL;
     </programlisting>
    </para>
  
Index: src/backend/commands/command.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/commands/command.c,v
retrieving revision 1.166
diff -c -r1.166 command.c
*** src/backend/commands/command.c	2002/03/26 19:15:36	1.166
--- src/backend/commands/command.c	2002/03/27 06:12:06
***************
*** 544,549 ****
--- 544,861 ----
  	AlterTableCreateToastTable(myrelid, true);
  }
  
+ /*
+  * ALTER TABLE ALTER COLUMN DROP NOT NULL
+  */
+ void
+ AlterTableAlterColumnDropNotNull(const char *relationName,
+ 							bool inh, const char *colName)
+ {
+ 	Relation	rel;
+ 	HeapTuple	tuple;
+ 	int16		attnum;
+ 	Oid		myrelid;
+ 	Relation	attr_rel;
+ 	ScanKeyData 	scankeys[3];
+ 	HeapScanDesc 	scan;
+ 	List	   	*indexoidlist;
+ 	List	   	*indexoidscan;
+ 
+ 	rel = heap_openr(relationName, AccessExclusiveLock);
+ 	myrelid = RelationGetRelid(rel);
+ 
+ 	if (rel->rd_rel->relkind != RELKIND_RELATION)
+ 		elog(ERROR, "ALTER TABLE: relation \"%s\" is not a table",
+ 			 relationName);
+ 
+ 	if (!allowSystemTableMods && IsSystemRelationName(relationName))
+ 		elog(ERROR, "ALTER TABLE: relation \"%s\" is a system catalog",
+ 			 relationName);
+ 	if (!pg_class_ownercheck(myrelid, GetUserId()))
+ 		elog(ERROR, "ALTER TABLE: permission denied");
+ 
+ 	heap_close(rel, NoLock);
+ 
+ 	/*
+ 	 * Propagate to children if desired
+ 	 */
+ 	if (inh)
+ 	{
+ 		List	   *child,
+ 				   *children;
+ 
+ 		/* this routine is actually in the planner */
+ 		children = find_all_inheritors(myrelid);
+ 
+ 		/*
+ 		 * find_all_inheritors does the recursive search of the
+ 		 * inheritance hierarchy, so all we have to do is process all of
+ 		 * the relids in the list that it returns.
+ 		 */
+ 		foreach(child, children)
+ 		{
+ 			Oid			childrelid = lfirsti(child);
+ 
+ 			if (childrelid == myrelid)
+ 				continue;
+ 			rel = heap_open(childrelid, AccessExclusiveLock);
+ 			AlterTableAlterColumnDropNotNull(RelationGetRelationName(rel),
+ 										 false, colName);
+ 			heap_close(rel, AccessExclusiveLock);
+ 		}
+ 	}
+ 
+ 	/* -= now do the thing on this relation =- */
+ 
+ 	/* reopen the business */
+ 	rel = heap_openr(relationName, AccessExclusiveLock);
+ 
+ 	/*
+ 	 * get the number of the attribute
+ 	 */
+ 	tuple = SearchSysCache(ATTNAME,
+ 						   ObjectIdGetDatum(myrelid),
+ 						   PointerGetDatum(colName),
+ 						   0, 0);
+ 	if (!HeapTupleIsValid(tuple))
+ 		elog(ERROR, "ALTER TABLE: relation \"%s\" has no column \"%s\"",
+ 			 relationName, colName);
+ 
+ 	attnum = ((Form_pg_attribute) GETSTRUCT(tuple))->attnum;
+ 	ReleaseSysCache(tuple);
+ 
+ 	/* Prevent them from altering a system attribute */
+ 	if (attnum < 0)
+ 		elog(ERROR, "ALTER TABLE: Cannot alter system attribute \"%s\"",
+ 			colName);
+ 
+ 	/* 
+ 	 * Check that the attribute is not in a primary key
+ 	 */
+ 		
+ 	/* Loop over all indices on the relation */
+ 	indexoidlist = RelationGetIndexList(rel);
+ 
+ 	foreach(indexoidscan, indexoidlist)
+ 	{
+ 		Oid		indexoid = lfirsti(indexoidscan);
+ 		HeapTuple	indexTuple;
+ 		Form_pg_index 	indexStruct;
+ 		int		i;
+ 
+ 		indexTuple = SearchSysCache(INDEXRELID,
+ 				  ObjectIdGetDatum(indexoid),
+ 									0, 0, 0);
+ 
+ 		if (!HeapTupleIsValid(indexTuple))
+ 			elog(ERROR, "ALTER TABLE: Index \"%u\" not found",
+ 				 indexoid);
+ 		indexStruct = (Form_pg_index) GETSTRUCT(indexTuple);
+ 
+ 		/* If the index is not a primary key, skip the check */
+ 		if (!indexStruct->indisprimary) continue;
+ 
+ 		/*
+ 		 * Loop over each attribute in the primary key and
+ 		 * see if it matches the to-be-altered attribute
+ 		 */
+ 		for (i = 0; i < INDEX_MAX_KEYS &&
+ 			     indexStruct->indkey[i] != InvalidAttrNumber; i++)
+ 		{
+ 			if (indexStruct->indkey[i] == attnum)
+ 				elog(ERROR, "ALTER TABLE: Attribute \"%s\" is in a primary key", colName);
+ 		}
+ 	
+ 		ReleaseSysCache(indexTuple);
+ 	}
+ 
+ 	freeList(indexoidlist);
+ 
+ 	/*
+ 	 * Now, actually perform the catalog change
+ 	 */
+ 	attr_rel = heap_openr(AttributeRelationName, RowExclusiveLock);
+ 	ScanKeyEntryInitialize(&scankeys[0], 0x0,
+ 						   Anum_pg_attribute_attrelid, F_OIDEQ,
+ 						   ObjectIdGetDatum(myrelid));
+ 	ScanKeyEntryInitialize(&scankeys[1], 0x0,
+ 						   Anum_pg_attribute_attnum, F_INT2EQ,
+ 						   Int16GetDatum(attnum));
+ 	ScanKeyEntryInitialize(&scankeys[2], 0x0,
+ 						   Anum_pg_attribute_attnotnull, F_BOOLEQ,
+ 						   BoolGetDatum(true));
+ 
+ 	scan = heap_beginscan(attr_rel, false, SnapshotNow, 3, scankeys);
+ 	AssertState(scan != NULL);
+ 
+ 	if (HeapTupleIsValid(tuple = heap_getnext(scan, 0)))
+ 	{
+ 		HeapTuple	newtuple;
+ 		Relation	irelations[Num_pg_attr_indices];
+ 
+ 		/* update to false */
+ 		newtuple = heap_copytuple(tuple);
+ 		((Form_pg_attribute) GETSTRUCT(newtuple))->attnotnull = FALSE;
+ 		simple_heap_update(attr_rel, &tuple->t_self, newtuple);
+ 
+ 		/* keep the system catalog indices current */
+ 		CatalogOpenIndices(Num_pg_attr_indices, Name_pg_attr_indices, irelations);
+ 		CatalogIndexInsert(irelations, Num_pg_attr_indices, attr_rel, newtuple);
+ 		CatalogCloseIndices(Num_pg_attr_indices, irelations);
+ 	}
+ 
+ 	heap_endscan(scan);
+ 	heap_close(attr_rel, NoLock);
+ 
+ 	heap_close(rel, NoLock);
+ }
+ 
+ /*
+  * ALTER TABLE ALTER COLUMN SET NOT NULL
+  */
+ void
+ AlterTableAlterColumnNotNull(const char *relationName,
+ 							bool inh, const char *colName)
+ {
+ 	Relation	rel;
+ 	HeapTuple	tuple;
+ 	int16		attnum;
+ 	Oid		myrelid;
+ 	Relation	attr_rel;
+ 	ScanKeyData 	scankeys[3];
+ 	HeapScanDesc 	scan;
+ 	/* variables for table scan */	
+ 	bool		isnull = false;
+ 	TupleDesc	tupdesc;
+ 	Datum 		d;
+ 
+ 	rel = heap_openr(relationName, AccessExclusiveLock);
+ 	myrelid = RelationGetRelid(rel);
+ 
+ 	if (rel->rd_rel->relkind != RELKIND_RELATION)
+ 		elog(ERROR, "ALTER TABLE: relation \"%s\" is not a table",
+ 			 relationName);
+ 
+ 	if (!allowSystemTableMods && IsSystemRelationName(relationName))
+ 		elog(ERROR, "ALTER TABLE: relation \"%s\" is a system catalog",
+ 			 relationName);
+ 	if (!pg_class_ownercheck(myrelid, GetUserId()))
+ 		elog(ERROR, "ALTER TABLE: permission denied");
+ 
+ 	heap_close(rel, NoLock);
+ 
+ 	/*
+ 	 * Propagate to children if desired
+ 	 */
+ 	if (inh)
+ 	{
+ 		List	   *child,
+ 				   *children;
+ 
+ 		/* this routine is actually in the planner */
+ 		children = find_all_inheritors(myrelid);
+ 
+ 		/*
+ 		 * find_all_inheritors does the recursive search of the
+ 		 * inheritance hierarchy, so all we have to do is process all of
+ 		 * the relids in the list that it returns.
+ 		 */
+ 		foreach(child, children)
+ 		{
+ 			Oid			childrelid = lfirsti(child);
+ 
+ 			if (childrelid == myrelid)
+ 				continue;
+ 			rel = heap_open(childrelid, AccessExclusiveLock);
+ 			AlterTableAlterColumnNotNull(RelationGetRelationName(rel),
+ 										 false, colName);
+ 			heap_close(rel, AccessExclusiveLock);
+ 		}
+ 	}
+ 
+ 	/* -= now do the thing on this relation =- */
+ 
+ 	/* reopen the business */
+ 	rel = heap_openr(relationName, AccessExclusiveLock);
+ 	tupdesc = RelationGetDescr(rel);
+ 
+ 	/*
+ 	 * get the number of the attribute
+ 	 */
+ 	tuple = SearchSysCache(ATTNAME,
+ 						   ObjectIdGetDatum(myrelid),
+ 						   PointerGetDatum(colName),
+ 						   0, 0);
+ 	if (!HeapTupleIsValid(tuple))
+ 		elog(ERROR, "ALTER TABLE: relation \"%s\" has no column \"%s\"",
+ 			 relationName, colName);
+ 
+ 	attnum = ((Form_pg_attribute) GETSTRUCT(tuple))->attnum;
+ 	ReleaseSysCache(tuple);
+ 
+ 	/* Prevent them from altering a system attribute */
+ 	if (attnum < 0)
+ 		elog(ERROR, "ALTER TABLE: Cannot alter system attribute \"%s\"",
+ 			colName);
+ 
+ 	/*
+ 	 * Perform a scan to ensure that there are no NULL
+ 	 * values already in the relation
+ 	 */
+ 
+ 	scan = heap_beginscan(rel, false, SnapshotNow, 0, NULL);
+ 
+ 	while (HeapTupleIsValid(tuple = heap_getnext(scan, 0)))
+ 	{
+ 		d = heap_getattr(tuple, attnum, tupdesc, &isnull);
+ 
+ 		if (isnull)
+ 			elog(ERROR, "ALTER TABLE: Attribute \"%s\" contains NULL values", colName);
+ 	}
+ 
+ 	heap_endscan(scan);
+ 
+ 	/*
+ 	 * Update attribute to be NOT NULL
+ 	 */
+ 	 
+ 	attr_rel = heap_openr(AttributeRelationName, RowExclusiveLock);
+ 	ScanKeyEntryInitialize(&scankeys[0], 0x0,
+ 						   Anum_pg_attribute_attrelid, F_OIDEQ,
+ 						   ObjectIdGetDatum(myrelid));
+ 	ScanKeyEntryInitialize(&scankeys[1], 0x0,
+ 						   Anum_pg_attribute_attnum, F_INT2EQ,
+ 						   Int16GetDatum(attnum));
+ 	ScanKeyEntryInitialize(&scankeys[2], 0x0,
+ 						   Anum_pg_attribute_attnotnull, F_BOOLEQ,
+ 						   BoolGetDatum(false));
+ 
+ 	scan = heap_beginscan(attr_rel, false, SnapshotNow, 3, scankeys);
+ 	AssertState(scan != NULL);
+ 
+ 	if (HeapTupleIsValid(tuple = heap_getnext(scan, 0)))
+ 	{
+ 		HeapTuple	newtuple;
+ 		Relation	irelations[Num_pg_attr_indices];
+ 
+ 		/* update to false */
+ 		newtuple = heap_copytuple(tuple);
+ 		((Form_pg_attribute) GETSTRUCT(newtuple))->attnotnull = TRUE;
+ 		simple_heap_update(attr_rel, &tuple->t_self, newtuple);
+ 
+ 		/* keep the system catalog indices current */
+ 		CatalogOpenIndices(Num_pg_attr_indices, Name_pg_attr_indices, irelations);
+ 		CatalogIndexInsert(irelations, Num_pg_attr_indices, attr_rel, newtuple);
+ 		CatalogCloseIndices(Num_pg_attr_indices, irelations);
+ 	}
+ 
+ 	heap_endscan(scan);
+ 	heap_close(attr_rel, NoLock);
+ 
+ 	heap_close(rel, NoLock);		
+ }
+ 
+ 
  
  /*
   * ALTER TABLE ALTER COLUMN SET/DROP DEFAULT
Index: src/backend/parser/gram.y
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.296
diff -c -r2.296 gram.y
*** src/backend/parser/gram.y	2002/03/22 02:56:33	2.296
--- src/backend/parser/gram.y	2002/03/27 06:12:13
***************
*** 1114,1119 ****
--- 1114,1137 ----
  					n->def = $7;
  					$$ = (Node *)n;
  				}
+ /* ALTER TABLE <relation> ALTER [COLUMN] <colname> DROP NOT NULL */
+                 | ALTER TABLE relation_expr ALTER opt_column ColId DROP NOT NULL_P
+                                 {
+                                         AlterTableStmt *n = makeNode(AlterTableStmt);
+                                         n->subtype = 'N';
+                                         n->relation = $3;
+                                         n->name = $6;
+                                         $$ = (Node *)n;
+                                 }
+ /* ALTER TABLE <relation> ALTER [COLUMN] <colname> SET NOT NULL */
+                 | ALTER TABLE relation_expr ALTER opt_column ColId SET NOT NULL_P
+                                 {
+                                         AlterTableStmt *n = makeNode(AlterTableStmt);
+                                         n->subtype = 'O';
+                                         n->relation = $3;
+                                         n->name = $6;
+                                         $$ = (Node *)n;
+                                 }
  /* ALTER TABLE <relation> ALTER [COLUMN] <colname> SET STATISTICS <Iconst> */
  		| ALTER TABLE relation_expr ALTER opt_column ColId SET STATISTICS Iconst
  				{
Index: src/backend/tcop/utility.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/tcop/utility.c,v
retrieving revision 1.140
diff -c -r1.140 utility.c
*** src/backend/tcop/utility.c	2002/03/26 19:16:03	1.140
--- src/backend/tcop/utility.c	2002/03/27 06:12:14
***************
*** 439,444 ****
--- 439,454 ----
  													 stmt->name,
  													 stmt->def);
  						break;
+ 					case 'N':	/* ALTER COLUMN DROP NOT NULL */
+ 						AlterTableAlterColumnDropNotNull(stmt->relation->relname,
+ 										interpretInhOption((stmt->relation)->inhOpt),
+ 													stmt->name);
+ 						break;
+ 					case 'O':	/* ALTER COLUMN SET NOT NULL */
+ 						AlterTableAlterColumnNotNull(stmt->relation->relname,
+ 										interpretInhOption((stmt->relation)->inhOpt),
+ 													stmt->name);
+ 						break;
  					case 'S':	/* ALTER COLUMN STATISTICS */
  					case 'M':   /* ALTER COLUMN STORAGE */
  						AlterTableAlterColumnFlags(stmt->relation->relname,
Index: src/include/commands/command.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/commands/command.h,v
retrieving revision 1.35
diff -c -r1.35 command.h
*** src/include/commands/command.h	2002/03/26 19:16:40	1.35
--- src/include/commands/command.h	2002/03/27 06:12:15
***************
*** 47,52 ****
--- 47,58 ----
  							 bool inh, const char *colName,
  							 Node *newDefault);
  
+ extern void AlterTableAlterColumnDropNotNull(const char *relationName,
+ 							 bool inh, const char *colName);
+ 
+ extern void AlterTableAlterColumnNotNull(const char *relationName,
+ 							 bool inh, const char *colName);
+ 
  extern void AlterTableAlterColumnFlags(const char *relationName,
  								bool inh, const char *colName,
  								Node *flagValue, const char *flagType);
Index: src/include/nodes/parsenodes.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/nodes/parsenodes.h,v
retrieving revision 1.165
diff -c -r1.165 parsenodes.h
*** src/include/nodes/parsenodes.h	2002/03/26 19:16:53	1.165
--- src/include/nodes/parsenodes.h	2002/03/27 06:12:17
***************
*** 719,724 ****
--- 719,726 ----
  	char		subtype;		/*------------
  								 *	A = add column
  								 *	T = alter column default
+ 								 *	N = alter column drop not null
+ 								 *	O = alter column set not null
  								 *	S = alter column statistics
  								 *  M = alter column storage
  								 *	D = drop column
Index: src/interfaces/ecpg/preproc/preproc.y
===================================================================
RCS file: /projects/cvsroot/pgsql/src/interfaces/ecpg/preproc/preproc.y,v
retrieving revision 1.184
diff -c -r1.184 preproc.y
*** src/interfaces/ecpg/preproc/preproc.y	2002/03/21 09:42:50	1.184
--- src/interfaces/ecpg/preproc/preproc.y	2002/03/27 06:12:23
***************
*** 907,912 ****
--- 907,918 ----
  /* ALTER TABLE <relation> ALTER [COLUMN] <colname> {SET DEFAULT <expr>|DROP DEFAULT} */
  		| ALTER TABLE relation_expr ALTER opt_column ColId alter_column_default
  			{ $$ = cat_str(6, make_str("alter table"), $3, make_str("alter"), $5, $6, $7); }
+ /* ALTER TABLE <relation> ALTER [COLUMN] <colname> DROP NOT NULL */
+ 		| ALTER TABLE relation_expr ALTER opt_column ColId DROP NOT NULL_P
+ 			{ $$ = cat_str(5, make_str("alter table"), $3, make_str("alter"), $5, $6); }
+ /* ALTER TABLE <relation> ALTER [COLUMN] <colname> SET NOT NULL */
+ 		| ALTER TABLE relation_expr ALTER opt_column ColId SET NOT NULL_P
+ 			{ $$ = cat_str(5, make_str("alter table"), $3, make_str("alter"), $5, $6); }
  /* ALTER TABLE <relation> ALTER [COLUMN] <colname> SET STATISTICS <Iconst> */
  		| ALTER TABLE relation_expr ALTER opt_column ColId SET STATISTICS Iconst
  			{ $$ = cat_str(7, make_str("alter table"), $3, make_str("alter"), $5, $6, make_str("set statistics"), $9); }
Index: src/test/regress/expected/alter_table.out
===================================================================
RCS file: /projects/cvsroot/pgsql/src/test/regress/expected/alter_table.out,v
retrieving revision 1.34
diff -c -r1.34 alter_table.out
*** src/test/regress/expected/alter_table.out	2002/03/21 16:02:09	1.34
--- src/test/regress/expected/alter_table.out	2002/03/27 06:12:24
***************
*** 578,580 ****
--- 578,651 ----
  insert into atacc1 (test2, test) values (1, NULL);
  ERROR:  ExecAppend: Fail to add null value in not null attribute test
  drop table atacc1;
+ -- alter table / alter column [set/drop] not null tests
+ -- try altering system catalogs, should fail
+ alter table pg_class alter column relname drop not null;
+ ERROR:  ALTER TABLE: relation "pg_class" is a system catalog
+ alter table pg_class alter relname set not null;
+ ERROR:  ALTER TABLE: relation "pg_class" is a system catalog
+ -- try altering non-existent table, should fail
+ alter table foo alter column bar set not null;
+ ERROR:  Relation "foo" does not exist
+ alter table foo alter column bar drop not null;
+ ERROR:  Relation "foo" does not exist
+ -- test setting columns to null and not null and vice versa
+ -- test checking for null values and primary key
+ create table atacc1 (test int not null);
+ alter table atacc1 add constraint "atacc1_pkey" primary key (test);
+ NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index 'atacc1_pkey' for table 'atacc1'
+ alter table atacc1 alter column test drop not null;
+ ERROR:  ALTER TABLE: Attribute "test" is in a primary key
+ drop index atacc1_pkey;
+ alter table atacc1 alter column test drop not null;
+ insert into atacc1 values (null);
+ alter table atacc1 alter test set not null;
+ ERROR:  ALTER TABLE: Attribute "test" contains NULL values
+ delete from atacc1;
+ alter table atacc1 alter test set not null;
+ -- try altering a non-existent column, should fail
+ alter table atacc1 alter bar set not null;
+ ERROR:  ALTER TABLE: relation "atacc1" has no column "bar"
+ alter table atacc1 alter bar drop not null;
+ ERROR:  ALTER TABLE: relation "atacc1" has no column "bar"
+ -- try altering the oid column, should fail
+ alter table atacc1 alter oid set not null;
+ ERROR:  ALTER TABLE: Cannot alter system attribute "oid"
+ alter table atacc1 alter oid drop not null;
+ ERROR:  ALTER TABLE: Cannot alter system attribute "oid"
+ -- try creating a view and altering that, should fail
+ create view myview as select * from atacc1;
+ alter table myview alter column test drop not null;
+ ERROR:  ALTER TABLE: relation "myview" is not a table
+ alter table myview alter column test set not null;
+ ERROR:  ALTER TABLE: relation "myview" is not a table
+ drop view myview;
+ drop table atacc1;
+ -- test inheritance
+ create table parent (a int);
+ create table child (b varchar(255)) inherits (parent);
+ alter table parent alter a set not null;
+ insert into parent values (NULL);
+ ERROR:  ExecAppend: Fail to add null value in not null attribute a
+ insert into child (a, b) values (NULL, 'foo');
+ ERROR:  ExecAppend: Fail to add null value in not null attribute a
+ alter table parent alter a drop not null;
+ insert into parent values (NULL);
+ insert into child (a, b) values (NULL, 'foo');
+ alter table only parent alter a set not null;
+ ERROR:  ALTER TABLE: Attribute "a" contains NULL values
+ alter table child alter a set not null;
+ ERROR:  ALTER TABLE: Attribute "a" contains NULL values
+ delete from parent;
+ alter table only parent alter a set not null;
+ insert into parent values (NULL);
+ ERROR:  ExecAppend: Fail to add null value in not null attribute a
+ alter table child alter a set not null;
+ insert into child (a, b) values (NULL, 'foo');
+ ERROR:  ExecAppend: Fail to add null value in not null attribute a
+ delete from child;
+ alter table child alter a set not null;
+ insert into child (a, b) values (NULL, 'foo');
+ ERROR:  ExecAppend: Fail to add null value in not null attribute a
+ drop table child;
+ drop table parent;
Index: src/test/regress/sql/alter_table.sql
===================================================================
RCS file: /projects/cvsroot/pgsql/src/test/regress/sql/alter_table.sql,v
retrieving revision 1.21
diff -c -r1.21 alter_table.sql
*** src/test/regress/sql/alter_table.sql	2002/03/19 12:59:08	1.21
--- src/test/regress/sql/alter_table.sql	2002/03/27 06:12:25
***************
*** 452,454 ****
--- 452,515 ----
  insert into atacc1 (test2, test) values (2, 3);
  insert into atacc1 (test2, test) values (1, NULL);
  drop table atacc1;
+ 
+ -- alter table / alter column [set/drop] not null tests
+ -- try altering system catalogs, should fail
+ alter table pg_class alter column relname drop not null;
+ alter table pg_class alter relname set not null;
+ 
+ -- try altering non-existent table, should fail
+ alter table foo alter column bar set not null;
+ alter table foo alter column bar drop not null;
+ 
+ -- test setting columns to null and not null and vice versa
+ -- test checking for null values and primary key
+ create table atacc1 (test int not null);
+ alter table atacc1 add constraint "atacc1_pkey" primary key (test);
+ alter table atacc1 alter column test drop not null;
+ drop index atacc1_pkey;
+ alter table atacc1 alter column test drop not null;
+ insert into atacc1 values (null);
+ alter table atacc1 alter test set not null;
+ delete from atacc1;
+ alter table atacc1 alter test set not null;
+ 
+ -- try altering a non-existent column, should fail
+ alter table atacc1 alter bar set not null;
+ alter table atacc1 alter bar drop not null;
+ 
+ -- try altering the oid column, should fail
+ alter table atacc1 alter oid set not null;
+ alter table atacc1 alter oid drop not null;
+ 
+ -- try creating a view and altering that, should fail
+ create view myview as select * from atacc1;
+ alter table myview alter column test drop not null;
+ alter table myview alter column test set not null;
+ drop view myview;
+ 
+ drop table atacc1;
+ 
+ -- test inheritance
+ create table parent (a int);
+ create table child (b varchar(255)) inherits (parent);
+ 
+ alter table parent alter a set not null;
+ insert into parent values (NULL);
+ insert into child (a, b) values (NULL, 'foo');
+ alter table parent alter a drop not null;
+ insert into parent values (NULL);
+ insert into child (a, b) values (NULL, 'foo');
+ alter table only parent alter a set not null;
+ alter table child alter a set not null;
+ delete from parent;
+ alter table only parent alter a set not null;
+ insert into parent values (NULL);
+ alter table child alter a set not null;
+ insert into child (a, b) values (NULL, 'foo');
+ delete from child;
+ alter table child alter a set not null;
+ insert into child (a, b) values (NULL, 'foo');
+ drop table child;
+ drop table parent;
+