· 6 years ago · Apr 11, 2019, 10:58 PM
1From 7e8f0a7e6d38f8d362faef28ed00cf432fd0a267 Mon Sep 17 00:00:00 2001
2From: Daniel Gustafsson <daniel@yesql.se>
3Date: Mon, 1 Apr 2019 16:02:28 +0200
4Subject: [PATCH] Add COMMENT IS for columns in create/alter table
5
6This adds support for a MySQL-like syntax for setting comments on
7relation attributes during CREATE|ALTER TABLE statements:
8
9 CREATE TABLE t (a int COMMENT IS 'Column a');
10 ALTER TABLE t ADD COLUMN b int COMENT IS 'Column c';
11 ALTER TABLE t ALTER COLUMN b COMMENT IS 'Column b';
12
13The implementation is a proof-of-concept patch to illustrate the
14concept, there are parts missing.
15---
16 doc/src/sgml/ref/alter_table.sgml | 13 +++++++++
17 doc/src/sgml/ref/create_table.sgml | 13 ++++++++-
18 src/backend/commands/tablecmds.c | 43 ++++++++++++++++++++++++++++++
19 src/backend/nodes/copyfuncs.c | 1 +
20 src/backend/nodes/equalfuncs.c | 1 +
21 src/backend/nodes/makefuncs.c | 1 +
22 src/backend/parser/gram.y | 32 +++++++++++++++++++---
23 src/include/nodes/parsenodes.h | 6 +++--
24 src/test/regress/expected/alter_table.out | 13 +++++----
25 src/test/regress/expected/create_table.out | 10 +++++++
26 src/test/regress/sql/alter_table.sql | 8 ++++--
27 src/test/regress/sql/create_table.sql | 5 ++++
28 12 files changed, 133 insertions(+), 13 deletions(-)
29
30diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
31index e360728c02..ec989fc366 100644
32--- a/doc/src/sgml/ref/alter_table.sgml
33+++ b/doc/src/sgml/ref/alter_table.sgml
34@@ -53,6 +53,7 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
35 ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> SET ( <replaceable class="parameter">attribute_option</replaceable> = <replaceable class="parameter">value</replaceable> [, ... ] )
36 ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> RESET ( <replaceable class="parameter">attribute_option</replaceable> [, ... ] )
37 ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
38+ ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> COMMENT IS <replaceable class="parameter">comment_text</replaceable>
39 ADD <replaceable class="parameter">table_constraint</replaceable> [ NOT VALID ]
40 ADD <replaceable class="parameter">table_constraint_using_index</replaceable>
41 ALTER CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
42@@ -359,6 +360,18 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
43 </listitem>
44 </varlistentry>
45
46+ <varlistentry>
47+ <term>
48+ <literal>COMMENT IS <replaceable class="parameter">comment_text</replaceable></literal>
49+ </term>
50+ <listitem>
51+ <para>
52+ Set a comment on the column. See <xref linkend="sql-comment"> for more
53+ info on comments.
54+ </para>
55+ </listitem>
56+ </varlistentry>
57+
58 <varlistentry>
59 <term><literal>ADD <replaceable class="parameter">table_constraint</replaceable> [ NOT VALID ]</literal></term>
60 <listitem>
61diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
62index 166078410c..ff3cbb7acb 100644
63--- a/doc/src/sgml/ref/create_table.sgml
64+++ b/doc/src/sgml/ref/create_table.sgml
65@@ -22,7 +22,7 @@ PostgreSQL documentation
66 <refsynopsisdiv>
67 <synopsis>
68 CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] <replaceable class="parameter">table_name</replaceable> ( [
69- { <replaceable class="parameter">column_name</replaceable> <replaceable class="parameter">data_type</replaceable> [ COLLATE <replaceable>collation</replaceable> ] [ <replaceable class="parameter">column_constraint</replaceable> [ ... ] ]
70+ { <replaceable class="parameter">column_name</replaceable> <replaceable class="parameter">data_type</replaceable> [ COLLATE <replaceable>collation</replaceable> ] [ <replaceable class="parameter">column_constraint</replaceable> [ ... ] ] [ COMMENT <replaceable>comment_text</replaceable> ]
71 | <replaceable>table_constraint</replaceable>
72 | LIKE <replaceable>source_table</replaceable> [ <replaceable>like_option</replaceable> ... ] }
73 [, ... ]
74@@ -284,6 +284,17 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
75 </listitem>
76 </varlistentry>
77
78+ <varlistentry>
79+ <term><literal>COMMENT <replaceable>comment_text</replaceable></literal></term>
80+ <listitem>
81+ <para>
82+ The optional <literal>COMMENT<literal/> clause adds a comment on
83+ the column. See <xref linkend="sql-comment"> for more info
84+ on comments.
85+ </para>
86+ </listitem>
87+ </varlistentry>
88+
89 <varlistentry>
90 <term><literal>INHERITS ( <replaceable>parent_table</replaceable> [, ... ] )</literal></term>
91 <listitem>
92diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
93index 16492a23c7..2c47773456 100644
94--- a/src/backend/commands/tablecmds.c
95+++ b/src/backend/commands/tablecmds.c
96@@ -501,6 +501,8 @@ static void refuseDupeIndexAttach(Relation parentIdx, Relation partIdx,
97 Relation partitionTbl);
98 static void update_relispartition(Relation classRel, Oid relationId,
99 bool newval);
100+static void ATExecColumnComment(Relation rel, const char *colName,
101+ const char *comment);
102
103
104 /* ----------------------------------------------------------------
105@@ -1084,6 +1086,20 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
106 */
107 relation_close(rel, NoLock);
108
109+ /*
110+ * Create any comments associated with the attributes.
111+ */
112+ attnum = 0;
113+ foreach(listptr, stmt->tableElts)
114+ {
115+ ColumnDef *colDef = lfirst(listptr);
116+
117+ attnum++;
118+ if (colDef->comment)
119+ CreateComments(relationId, RelationRelationId, attnum,
120+ colDef->comment->comment);
121+ }
122+
123 return address;
124 }
125
126@@ -3551,6 +3567,7 @@ AlterTableGetLockLevel(List *cmds)
127 case AT_EnableReplicaRule: /* may change SELECT rules */
128 case AT_EnableRule: /* may change SELECT rules */
129 case AT_DisableRule: /* may change SELECT rules */
130+ case AT_SetColumnComment: /* COMMENT IS .. */
131 cmd_lockmode = AccessExclusiveLock;
132 break;
133
134@@ -4048,6 +4065,10 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
135 /* No command-specific prep needed */
136 pass = AT_PASS_MISC;
137 break;
138+ case AT_SetColumnComment:
139+ ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE);
140+ pass = AT_PASS_MISC;
141+ break;
142 default: /* oops */
143 elog(ERROR, "unrecognized alter table type: %d",
144 (int) cmd->subtype);
145@@ -4387,6 +4408,9 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, Relation rel,
146 Assert(rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE);
147 ATExecDetachPartition(rel, ((PartitionCmd *) cmd->def)->name);
148 break;
149+ case AT_SetColumnComment:
150+ ATExecColumnComment(rel, cmd->name, ((CommentStmt *) cmd->def)->comment);
151+ break;
152 default: /* oops */
153 elog(ERROR, "unrecognized alter table type: %d",
154 (int) cmd->subtype);
155@@ -5678,6 +5702,15 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel,
156 tab->rewrite |= AT_REWRITE_DEFAULT_VAL;
157 }
158
159+ /*
160+ * Store the COMMENT, if any, in the catalogs
161+ */
162+ if (colDef->comment)
163+ {
164+ CreateComments(RelationGetRelid(rel), RelationRelationId,
165+ attribute.attnum, colDef->comment->comment);
166+ }
167+
168 /*
169 * Tell Phase 3 to fill in the default expression, if there is one.
170 *
171@@ -6202,6 +6235,16 @@ NotNullImpliedByRelConstraints(Relation rel, Form_pg_attribute attr)
172 return false;
173 }
174
175+static void
176+ATExecColumnComment(Relation rel, const char *colName, const char *comment)
177+{
178+ Oid relid = RelationGetRelid(rel);
179+ AttrNumber attnum;
180+
181+ attnum = get_attnum(relid, colName);
182+ CreateComments(relid, RelationRelationId, attnum, comment);
183+}
184+
185 /*
186 * ALTER TABLE ALTER COLUMN SET/DROP DEFAULT
187 *
188diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
189index 84f9112add..516018c99c 100644
190--- a/src/backend/nodes/copyfuncs.c
191+++ b/src/backend/nodes/copyfuncs.c
192@@ -2893,6 +2893,7 @@ _copyColumnDef(const ColumnDef *from)
193 COPY_NODE_FIELD(constraints);
194 COPY_NODE_FIELD(fdwoptions);
195 COPY_LOCATION_FIELD(location);
196+ COPY_NODE_FIELD(comment);
197
198 return newnode;
199 }
200diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
201index 7eb9f1dd92..7530e85e74 100644
202--- a/src/backend/nodes/equalfuncs.c
203+++ b/src/backend/nodes/equalfuncs.c
204@@ -2570,6 +2570,7 @@ _equalColumnDef(const ColumnDef *a, const ColumnDef *b)
205 COMPARE_NODE_FIELD(constraints);
206 COMPARE_NODE_FIELD(fdwoptions);
207 COMPARE_LOCATION_FIELD(location);
208+ COMPARE_NODE_FIELD(comment);
209
210 return true;
211 }
212diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes/makefuncs.c
213index 7085ed2c4c..a66e151b3f 100644
214--- a/src/backend/nodes/makefuncs.c
215+++ b/src/backend/nodes/makefuncs.c
216@@ -504,6 +504,7 @@ makeColumnDef(const char *colname, Oid typeOid, int32 typmod, Oid collOid)
217 n->constraints = NIL;
218 n->fdwoptions = NIL;
219 n->location = -1;
220+ n->comment = NULL;
221
222 return n;
223 }
224diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
225index 28f62de97e..19683f70e3 100644
226--- a/src/backend/parser/gram.y
227+++ b/src/backend/parser/gram.y
228@@ -543,6 +543,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
229 %type <ival> TableLikeOptionList TableLikeOption
230 %type <list> ColQualList
231 %type <node> ColConstraint ColConstraintElem ConstraintAttr
232+%type <node> OptColComment ColComment
233 %type <ival> key_actions key_delete key_match key_update key_action
234 %type <ival> ConstraintAttributeSpec ConstraintAttributeElem
235 %type <str> ExistingIndex
236@@ -2219,6 +2220,15 @@ alter_table_cmd:
237 n->missing_ok = true;
238 $$ = (Node *)n;
239 }
240+ /* ALTER TABLE <name> ALTER [COLUMN] <colname> COMMENT IS <comment> */
241+ | ALTER opt_column ColId ColComment
242+ {
243+ AlterTableCmd *n = makeNode(AlterTableCmd);
244+ n->subtype = AT_SetColumnComment;
245+ n->name = $3;
246+ n->def = (Node *) $4;
247+ $$ = (Node *) n;
248+ }
249 /* ALTER TABLE <name> DROP [COLUMN] IF EXISTS <colname> [RESTRICT|CASCADE] */
250 | DROP opt_column IF_P EXISTS ColId opt_drop_behavior
251 {
252@@ -3326,7 +3336,7 @@ TypedTableElement:
253 | TableConstraint { $$ = $1; }
254 ;
255
256-columnDef: ColId Typename create_generic_options ColQualList
257+columnDef: ColId Typename OptColComment create_generic_options ColQualList
258 {
259 ColumnDef *n = makeNode(ColumnDef);
260 n->colname = $1;
261@@ -3339,10 +3349,11 @@ columnDef: ColId Typename create_generic_options ColQualList
262 n->raw_default = NULL;
263 n->cooked_default = NULL;
264 n->collOid = InvalidOid;
265- n->fdwoptions = $3;
266- SplitColQualList($4, &n->constraints, &n->collClause,
267+ n->fdwoptions = $4;
268+ SplitColQualList($5, &n->constraints, &n->collClause,
269 yyscanner);
270 n->location = @1;
271+ n->comment = (CommentStmt *) $3;
272 $$ = (Node *)n;
273 }
274 ;
275@@ -3390,6 +3401,21 @@ ColQualList:
276 | /*EMPTY*/ { $$ = NIL; }
277 ;
278
279+OptColComment:
280+ ColComment { $$ = $1; }
281+ | /* EMPTY */ { $$ = NULL; }
282+ ;
283+
284+ColComment: COMMENT IS comment_text
285+ {
286+ CommentStmt *n = makeNode(CommentStmt);
287+ n->objtype = OBJECT_COLUMN;
288+ n->object = NULL;
289+ n->comment = $3;
290+ $$ = (Node *) n;
291+ }
292+ ;
293+
294 ColConstraint:
295 CONSTRAINT name ColConstraintElem
296 {
297diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
298index e81c626913..091676baf8 100644
299--- a/src/include/nodes/parsenodes.h
300+++ b/src/include/nodes/parsenodes.h
301@@ -660,6 +660,7 @@ typedef struct ColumnDef
302 List *constraints; /* other constraints on column */
303 List *fdwoptions; /* per-column FDW options */
304 int location; /* parse location, or -1 if none/unknown */
305+ struct CommentStmt *comment; /* column comment if any */
306 } ColumnDef;
307
308 /*
309@@ -1811,7 +1812,8 @@ typedef enum AlterTableType
310 AT_DetachPartition, /* DETACH PARTITION */
311 AT_AddIdentity, /* ADD IDENTITY */
312 AT_SetIdentity, /* SET identity column options */
313- AT_DropIdentity /* DROP IDENTITY */
314+ AT_DropIdentity, /* DROP IDENTITY */
315+ AT_SetColumnComment /* column COMMENT IS */
316 } AlterTableType;
317
318 typedef struct ReplicaIdentityStmt
319@@ -1831,7 +1833,7 @@ typedef struct AlterTableCmd /* one subcommand of an ALTER TABLE */
320 * number */
321 RoleSpec *newowner;
322 Node *def; /* definition of new column, index,
323- * constraint, or parent table */
324+ * constraint, parent table or comment */
325 DropBehavior behavior; /* RESTRICT or CASCADE for DROP cases */
326 bool missing_ok; /* skip error if missing? */
327 } AlterTableCmd;
328diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
329index 2a26aa3a89..742c70b113 100644
330--- a/src/test/regress/expected/alter_table.out
331+++ b/src/test/regress/expected/alter_table.out
332@@ -2957,18 +2957,21 @@ DROP TABLE tt9;
333 CREATE TABLE comment_test (
334 id int,
335 positive_col int CHECK (positive_col > 0),
336- indexed_col int,
337+ indexed_col int COMMENT IS 'Attribute to index',
338 CONSTRAINT comment_test_pk PRIMARY KEY (id));
339 CREATE INDEX comment_test_index ON comment_test(indexed_col);
340+ALTER TABLE comment_test ALTER COLUMN positive_col COMMENT IS 'Positive attribute';
341 COMMENT ON COLUMN comment_test.id IS 'Column ''id'' on comment_test';
342 COMMENT ON INDEX comment_test_index IS 'Simple index on comment_test';
343 COMMENT ON CONSTRAINT comment_test_positive_col_check ON comment_test IS 'CHECK constraint on comment_test.positive_col';
344 COMMENT ON CONSTRAINT comment_test_pk ON comment_test IS 'PRIMARY KEY constraint of comment_test';
345 COMMENT ON INDEX comment_test_pk IS 'Index backing the PRIMARY KEY of comment_test';
346-SELECT col_description('comment_test'::regclass, 1) as comment;
347- comment
348------------------------------
349- Column 'id' on comment_test
350+SELECT col_description('comment_test'::regclass, 1) as id_comment,
351+ col_description('comment_test'::regclass, 2) as pos_comment,
352+ col_description('comment_test'::regclass, 3) as ind_comment;
353+ id_comment | pos_comment | ind_comment
354+-----------------------------+--------------------+--------------------
355+ Column 'id' on comment_test | Positive attribute | Attribute to index
356 (1 row)
357
358 SELECT indexrelid::regclass::text as index, obj_description(indexrelid, 'pg_class') as comment FROM pg_index where indrelid = 'comment_test'::regclass ORDER BY 1, 2;
359diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out
360index ad0cb32678..0739442237 100644
361--- a/src/test/regress/expected/create_table.out
362+++ b/src/test/regress/expected/create_table.out
363@@ -1119,3 +1119,13 @@ select tableoid::regclass from volatile_partbound_test;
364 (1 row)
365
366 drop table volatile_partbound_test;
367+-- comments on individual columns
368+create table columncomment (a int comment is 'column a', b int);
369+\d+ columncomment
370+ Table "public.columncomment"
371+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
372+--------+---------+-----------+----------+---------+---------+--------------+-------------
373+ a | integer | | | | plain | | column a
374+ b | integer | | | | plain | |
375+
376+drop table columncomment;
377diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
378index 5bda7febde..688a8cc725 100644
379--- a/src/test/regress/sql/alter_table.sql
380+++ b/src/test/regress/sql/alter_table.sql
381@@ -1854,17 +1854,21 @@ DROP TABLE tt9;
382 CREATE TABLE comment_test (
383 id int,
384 positive_col int CHECK (positive_col > 0),
385- indexed_col int,
386+ indexed_col int COMMENT IS 'Attribute to index',
387 CONSTRAINT comment_test_pk PRIMARY KEY (id));
388 CREATE INDEX comment_test_index ON comment_test(indexed_col);
389
390+ALTER TABLE comment_test ALTER COLUMN positive_col COMMENT IS 'Positive attribute';
391+
392 COMMENT ON COLUMN comment_test.id IS 'Column ''id'' on comment_test';
393 COMMENT ON INDEX comment_test_index IS 'Simple index on comment_test';
394 COMMENT ON CONSTRAINT comment_test_positive_col_check ON comment_test IS 'CHECK constraint on comment_test.positive_col';
395 COMMENT ON CONSTRAINT comment_test_pk ON comment_test IS 'PRIMARY KEY constraint of comment_test';
396 COMMENT ON INDEX comment_test_pk IS 'Index backing the PRIMARY KEY of comment_test';
397
398-SELECT col_description('comment_test'::regclass, 1) as comment;
399+SELECT col_description('comment_test'::regclass, 1) as id_comment,
400+ col_description('comment_test'::regclass, 2) as pos_comment,
401+ col_description('comment_test'::regclass, 3) as ind_comment;
402 SELECT indexrelid::regclass::text as index, obj_description(indexrelid, 'pg_class') as comment FROM pg_index where indrelid = 'comment_test'::regclass ORDER BY 1, 2;
403 SELECT conname as constraint, obj_description(oid, 'pg_constraint') as comment FROM pg_constraint where conrelid = 'comment_test'::regclass ORDER BY 1, 2;
404
405diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql
406index 751c0d39f5..0cdf908de2 100644
407--- a/src/test/regress/sql/create_table.sql
408+++ b/src/test/regress/sql/create_table.sql
409@@ -856,3 +856,8 @@ create table volatile_partbound_test2 partition of volatile_partbound_test for v
410 insert into volatile_partbound_test values (current_timestamp);
411 select tableoid::regclass from volatile_partbound_test;
412 drop table volatile_partbound_test;
413+
414+-- comments on individual columns
415+create table columncomment (a int comment is 'column a', b int);
416+\d+ columncomment
417+drop table columncomment;
418--
4192.14.1.145.gb3622a4ee