This patch implements ORACLE's COMMENT SQL command.

>From the ORACLE 7 SQL Language Reference Manual:
-----------------------------------------------------
COMMENT

Purpose:

To add a comment about a table, view, snapshot, or
column into the data dictionary.

Prerequisites:

The table, view, or snapshot must be in your own
schema
or you must have COMMENT ANY TABLE system privilege.

Syntax:

COMMENT ON [ TABLE table ] |
           [ COLUMN table.column] IS 'text'

You can effectively drop a comment from the database
by setting it to the empty string ''.
-----------------------------------------------------

Example:

COMMENT ON TABLE workorders IS
   'Maintains base records for workorder information';

COMMENT ON COLUMN workorders.hours IS
   'Number of hours the engineer worked on the task';

to drop a comment:

COMMENT ON COLUMN workorders.hours IS '';

The current patch will simply perform the insert into
pg_description, as per the TODO. And, of course, when
the table is dropped, any comments relating to it
or any of its attributes are also dropped. I haven't
looked at the ODBC source yet, but I do know from
an ODBC client standpoint that the standard does
support the notion of table and column comments.
Hopefully the ODBC driver is already fetching these
values from pg_description, but if not, it should be
trivial.

Hope this makes the grade,

Mike Mascari
(mascarim@yahoo.com)

6 files changed, 263 insertions, 16 deletions

diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index 0f3044b9a6a..bd0a8668d0f 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -7,7 +7,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/catalog/heap.c,v 1.103 1999/10/07 05:48:03 tgl Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/catalog/heap.c,v 1.104 1999/10/15 01:49:39 momjian Exp $
  *
  *
  * INTERFACE ROUTINES
@@ -38,6 +38,7 @@
 #include "catalog/index.h"
 #include "catalog/indexing.h"
 #include "catalog/pg_attrdef.h"
+#include "catalog/pg_description.h"
 #include "catalog/pg_index.h"
 #include "catalog/pg_inherits.h"
 #include "catalog/pg_ipl.h"
@@ -67,6 +68,7 @@ static void AddNewRelationTuple(Relation pg_class_desc,
 				  Relation new_rel_desc, Oid new_rel_oid, unsigned natts,
 					char relkind, char *temp_relname);
 static void AddToNoNameRelList(Relation r);
+
 static void DeleteAttributeTuples(Relation rel);
 static void DeleteRelationTuple(Relation rel);
 static void DeleteTypeTuple(Relation rel);
@@ -861,10 +863,11 @@ heap_create_with_catalog(char *relname,
  *		2)	remove inheritance information
  *		3)	remove indexes
  *		4)	remove pg_class tuple
- *		5)	remove pg_attribute tuples
- *		6)	remove pg_type tuples
- *		7)	RemoveConstraints ()
- *		8)	unlink relation
+ *		5)	remove pg_attribute tuples and related descriptions
+ *              6)      remove pg_description tuples
+ *		7)	remove pg_type tuples
+ *		8)	RemoveConstraints ()
+ *		9)	unlink relation
  *
  * old comments
  *		Except for vital relations, removes relation from
@@ -1269,18 +1272,152 @@ DeleteAttributeTuples(Relation rel)
 		 attnum++)
 	{
 		if (HeapTupleIsValid(tup = SearchSysCacheTupleCopy(ATTNUM,
-								 ObjectIdGetDatum(RelationGetRelid(rel)),
-												   Int16GetDatum(attnum),
+								   ObjectIdGetDatum(RelationGetRelid(rel)),
+								   Int16GetDatum(attnum),
 														   0, 0)))
 		{
-			heap_delete(pg_attribute_desc, &tup->t_self, NULL);
-			pfree(tup);
+		      DeleteComments(tup->t_data->t_oid);
+		      heap_delete(pg_attribute_desc, &tup->t_self, NULL);
+		      pfree(tup);
 		}
 	}
 
 	heap_close(pg_attribute_desc, RowExclusiveLock);
 }
 
+/* ----------------------------------------------------------
+ *  CreateComments
+ * 
+ *  This routine is handed the oid and the command associated
+ *  with that id and will insert, update, or delete (if the 
+ *  comment is an empty string or a NULL pointer) the associated
+ *  comment from the system cataloge, pg_description. 
+ *
+ * ----------------------------------------------------------
+ */
+
+void
+CreateComments(Oid oid, char *comment)
+{
+
+  Relation description;
+  TupleDesc tupDesc;
+  HeapScanDesc scan;
+  ScanKeyData entry;
+  HeapTuple desctuple, searchtuple;
+  Datum values[Natts_pg_description];
+  char nulls[Natts_pg_description];
+  char replaces[Natts_pg_description];
+  bool modified = false;
+  int i;
+
+  /*** Open pg_description, form a new tuple, if necessary ***/
+
+  description = heap_openr(DescriptionRelationName, RowExclusiveLock);
+  tupDesc = description->rd_att;
+  if ((comment != NULL) && (strlen(comment) > 0)) {
+    for (i = 0; i < Natts_pg_description; i++) {
+      nulls[i] = ' ';
+      replaces[i] = 'r';
+      values[i] = (Datum) NULL;
+    }
+    i = 0;
+    values[i++] = ObjectIdGetDatum(oid);
+    values[i++] = (Datum) fmgr(F_TEXTIN, comment);
+  }
+
+  /*** Now, open pg_description and attempt to find the old tuple ***/
+  
+  ScanKeyEntryInitialize(&entry, 0x0, Anum_pg_description_objoid, F_OIDEQ,
+			 ObjectIdGetDatum(oid));
+  scan = heap_beginscan(description, false, SnapshotNow, 1, &entry);
+  searchtuple = heap_getnext(scan, 0);
+
+  /*** If a previous tuple exists, either delete it or prepare a replacement ***/
+
+  if (HeapTupleIsValid(searchtuple)) {
+        
+    /*** If the comment is blank, call heap_delete, else heap_replace ***/
+
+    if ((comment == NULL) || (strlen(comment) == 0)) {
+      heap_delete(description, &searchtuple->t_self, NULL);
+    } else {
+      desctuple = heap_modifytuple(searchtuple, description, values, nulls, replaces);      
+      setheapoverride(true);
+      heap_replace(description, &searchtuple->t_self, desctuple, NULL);
+      setheapoverride(false);
+      modified = TRUE;
+    }
+
+  } else {    
+    desctuple = heap_formtuple(tupDesc, values, nulls);
+    heap_insert(description, desctuple);
+    modified = TRUE;
+  }
+
+  /*** Complete the scan, update indices, if necessary ***/
+
+  heap_endscan(scan);
+  
+  if (modified) {
+    if (RelationGetForm(description)->relhasindex) {
+      Relation idescs[Num_pg_description_indices];
+      
+      CatalogOpenIndices(Num_pg_description_indices, Name_pg_description_indices, idescs);
+      CatalogIndexInsert(idescs, Num_pg_description_indices, description, desctuple);
+      CatalogCloseIndices(Num_pg_description_indices, idescs);
+    }
+    pfree(desctuple);
+
+  }
+
+  heap_close(description, RowExclusiveLock);
+
+}
+    
+/* --------------------------------
+ *  DeleteComments
+ *
+ *  This routine is used to purge any comments 
+ *  associated with the Oid handed to this routine,
+ *  regardless of the actual object type. It is
+ *  called, for example, when a relation is destroyed.
+ * --------------------------------
+ */
+
+void 
+DeleteComments(Oid oid) 
+{
+
+  Relation description;
+  TupleDesc tupDesc;
+  ScanKeyData entry;
+  HeapScanDesc scan;
+  HeapTuple searchtuple;
+
+  description = heap_openr(DescriptionRelationName, RowExclusiveLock);
+  tupDesc = description->rd_att;
+
+  /*** Now, open pg_description and attempt to find the old tuple ***/
+  
+  ScanKeyEntryInitialize(&entry, 0x0, Anum_pg_description_objoid, F_OIDEQ,
+			 ObjectIdGetDatum(oid));
+  scan = heap_beginscan(description, false, SnapshotNow, 1, &entry);
+  searchtuple = heap_getnext(scan, 0);
+
+  /*** If a previous tuple exists, delete it ***/
+
+  if (HeapTupleIsValid(searchtuple)) {    
+    heap_delete(description, &searchtuple->t_self, NULL);
+  } 
+  
+  /*** Complete the scan, update indices, if necessary ***/
+
+  heap_endscan(scan);
+  heap_close(description, RowExclusiveLock);
+  
+}
+
 /* --------------------------------
  *		DeleteTypeTuple
  *
@@ -1471,6 +1608,13 @@ heap_destroy_with_catalog(char *relname)
 	 */
 	DeleteAttributeTuples(rel);
 
+	/* ----------------
+	 *      delete comments
+         * ----------------
+         */
+
+	DeleteComments(RelationGetRelid(rel));
+
 	if (istemp)
 		remove_temp_relation(rid);
 
diff --git a/src/backend/catalog/indexing.c b/src/backend/catalog/indexing.c
index 1e5c6252a4b..2caffcbcf5c 100644
--- a/src/backend/catalog/indexing.c
+++ b/src/backend/catalog/indexing.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/catalog/indexing.c,v 1.47 1999/09/30 10:31:42 wieck Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/catalog/indexing.c,v 1.48 1999/10/15 01:49:39 momjian Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -55,6 +55,9 @@ char	   *Name_pg_trigger_indices[Num_pg_trigger_indices] = {TriggerRelidIndex,
 	TriggerConstrNameIndex,
 	TriggerConstrRelidIndex};
 
+char       *Name_pg_description_indices[Num_pg_description_indices] = {DescriptionObjIndex};
+
+
 
 static HeapTuple CatalogIndexFetchTuple(Relation heapRelation,
 					   Relation idesc,
diff --git a/src/backend/commands/creatinh.c b/src/backend/commands/creatinh.c
index 0b2d2360b3c..9463d55a6a1 100644
--- a/src/backend/commands/creatinh.c
+++ b/src/backend/commands/creatinh.c
@@ -7,7 +7,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/commands/Attic/creatinh.c,v 1.48 1999/10/03 23:55:27 tgl Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/commands/Attic/creatinh.c,v 1.49 1999/10/15 01:49:39 momjian Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -16,10 +16,12 @@
 
 #include "access/heapam.h"
 #include "catalog/catname.h"
+#include "catalog/indexing.h"
 #include "catalog/heap.h"
 #include "catalog/pg_inherits.h"
 #include "catalog/pg_ipl.h"
 #include "catalog/pg_type.h"
+#include "catalog/pg_description.h"
 #include "commands/creatinh.h"
 #include "utils/syscache.h"
 
@@ -232,6 +234,52 @@ TruncateRelation(char *name)
 	heap_truncate(name);
 }
 
+/*------------------------------------------------------------------
+ * CommentRelation --
+ *                Adds a comment to pg_description for the associated
+ *                relation or relation attribute.
+ *
+ * Note:           
+ *                The comment is dropped on the relation or attribute if
+ *                the comment is an empty string.
+ *------------------------------------------------------------------
+ */
+void 
+CommentRelation(char *relname, char *attrname, char *comments) 
+{
+
+  Relation relation;
+  HeapTuple attrtuple;
+  Oid oid;
+
+  /*** First ensure relname is valid ***/
+
+  relation = heap_openr(relname, AccessShareLock);
+  
+  /*** Now, if an attribute was specified, fetch its oid, else use relation's oid ***/
+  
+  if (attrname != NULL) {
+    attrtuple = SearchSysCacheTuple(ATTNAME, ObjectIdGetDatum(relation->rd_id),
+				    PointerGetDatum(attrname), 0, 0);
+    if (!HeapTupleIsValid(attrtuple)) {
+      elog(ERROR, "CommentRelation: attribute \"%s\" is not an attribute of relation \"%s\"",
+	   attrname, relname);
+    }
+    oid = attrtuple->t_data->t_oid;
+  } else {
+    oid = RelationGetRelid(relation);
+  }
+  
+  /*** Call CreateComments() to create/drop the comments ***/
+
+  CreateComments(oid, comments);
+
+  /*** Now, close the heap relation ***/
+
+  heap_close(relation, AccessShareLock); 
+
+}
+
 /*
  * MergeAttributes
  *		Returns new schema given initial schema and supers.
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index a88b66c9707..16a4efa2baa 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -10,7 +10,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 2.108 1999/10/07 04:23:12 tgl Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 2.109 1999/10/15 01:49:41 momjian Exp $
  *
  * HISTORY
  *	  AUTHOR			DATE			MAJOR EVENT
@@ -118,7 +118,7 @@ Oid	param_type(int t); /* used in parse_expr.c */
 %type <node>	stmt,
 		AddAttrStmt, ClosePortalStmt,
 		CopyStmt, CreateStmt, CreateAsStmt, CreateSeqStmt, DefineStmt, DestroyStmt,
-		TruncateStmt,
+		TruncateStmt, CommentStmt,
 		ExtendStmt, FetchStmt,	GrantStmt, CreateTrigStmt, DropTrigStmt,
 		CreatePLangStmt, DropPLangStmt,
 		IndexStmt, ListenStmt, UnlistenStmt, LockStmt, OptimizableStmt,
@@ -314,7 +314,7 @@ Oid	param_type(int t); /* used in parse_expr.c */
  */
 %token	ABORT_TRANS, ACCESS, AFTER, AGGREGATE, ANALYZE,
 		BACKWARD, BEFORE, BINARY, 
-		CACHE, CLUSTER, COPY, CREATEDB, CREATEUSER, CYCLE,
+		CACHE, CLUSTER, COMMENT, COPY, CREATEDB, CREATEUSER, CYCLE,
 		DATABASE, DELIMITERS, DO,
 		EACH, ENCODING, EXCLUSIVE, EXPLAIN, EXTEND,
 		FORWARD, FUNCTION, HANDLER,
@@ -402,6 +402,7 @@ stmt :	  AddAttrStmt
 		| DefineStmt
 		| DestroyStmt		
 		| TruncateStmt
+		| CommentStmt
 		| DropPLangStmt
 		| DropTrigStmt
 		| DropUserStmt
@@ -1541,6 +1542,32 @@ TruncateStmt:  TRUNCATE TABLE relation_name
 
 /*****************************************************************************
  *
+ *             QUERY:
+ *                     comment on [ table <relname> | column <relname>.<attribute> ]
+ *                                is 'text'
+ *
+ *****************************************************************************/
+ 
+CommentStmt:	COMMENT ON COLUMN relation_name '.' attr_name IS Sconst
+			{
+				CommentStmt *n = makeNode(CommentStmt);
+				n->relname = $4;
+				n->attrname = $6;
+				n->comment = $8;
+				$$ = (Node *) n;
+			}
+		| COMMENT ON TABLE relation_name IS Sconst
+		        {
+				CommentStmt *n = makeNode(CommentStmt);
+				n->relname = $4;
+				n->attrname = NULL;
+				n->comment = $6;
+				$$ = (Node *) n;
+			}
+		;
+
+/*****************************************************************************
+ *
  *		QUERY:
  *			fetch/move [forward | backward] [ # | all ] [ in <portalname> ]
  *			fetch [ forward | backward | absolute | relative ]
@@ -5011,6 +5038,7 @@ ColId:  IDENT							{ $$ = $1; }
 		| BACKWARD						{ $$ = "backward"; }
 		| BEFORE						{ $$ = "before"; }
 		| CACHE							{ $$ = "cache"; }
+		| COMMENT						{ $$ = "comment"; }
 		| COMMITTED						{ $$ = "committed"; }
 		| CONSTRAINTS					{ $$ = "constraints"; }
 		| CREATEDB						{ $$ = "createdb"; }
@@ -5081,6 +5109,7 @@ ColId:  IDENT							{ $$ = $1; }
 		| TIMEZONE_HOUR					{ $$ = "timezone_hour"; }
 		| TIMEZONE_MINUTE				{ $$ = "timezone_minute"; }
 		| TRIGGER						{ $$ = "trigger"; }
+		| TRUNCATE						{ $$ = "truncate"; }
 		| TRUSTED						{ $$ = "trusted"; }
 		| TYPE_P						{ $$ = "type"; }
 		| VALID							{ $$ = "valid"; }
diff --git a/src/backend/parser/keywords.c b/src/backend/parser/keywords.c
index 3e30a47d12a..392e7509c8e 100644
--- a/src/backend/parser/keywords.c
+++ b/src/backend/parser/keywords.c
@@ -7,7 +7,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/parser/keywords.c,v 1.62 1999/09/29 16:06:08 wieck Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/parser/keywords.c,v 1.63 1999/10/15 01:49:41 momjian Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -60,6 +60,7 @@ static ScanKeyword ScanKeywords[] = {
 	{"coalesce", COALESCE},
 	{"collate", COLLATE},
 	{"column", COLUMN},
+	{"comment", COMMENT},
 	{"commit", COMMIT},
 	{"committed", COMMITTED},
 	{"constraint", CONSTRAINT},
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 0b8d1efcdde..ec617d75369 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -9,7 +9,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/tcop/utility.c,v 1.69 1999/09/30 01:12:36 tgl Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/tcop/utility.c,v 1.70 1999/10/15 01:49:43 momjian Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -233,6 +233,28 @@ ProcessUtility(Node *parsetree,
 			}
 			break;
 
+	case T_CommentStmt:
+	  {
+	    
+	    CommentStmt *statement;
+	    
+	    statement = ((CommentStmt *) parsetree);
+	    
+	    PS_SET_STATUS(commandTag = "COMMENT");
+	    CHECK_IF_ABORTED();
+
+#ifndef NO_SECURITY
+	    if (!pg_ownercheck(userName, statement->relname, RELNAME))
+	      elog(ERROR, "you do not own class \"%s\"", statement->relname);
+#endif
+
+	    CommentRelation(statement->relname, statement->attrname, 
+			    statement->comment);
+	  }
+	  break;
+	    
+
+
 		case T_CopyStmt:
 			{
 				CopyStmt   *stmt = (CopyStmt *) parsetree;