Обсуждение: Column COMMENTs in CREATE TABLE?

Hi,

Currently we have CREATE TABLE statements in a git repository that look 
roughly like this:

CREATE TABLE foo(  -- the first field  f1 int NOT NULL,  -- the second field  f2 int NOT NULL,
...
);

But the problem is that those comments don't obviously make it all the 
way to the database, so e.g.  \d+ tblname  won't show you that precious 
information.  If you want them to make it all the way to the database, 
you'd have to add COMMENT ON statements *after* the CREATE TABLE, which 
means that either column comments have to be maintained twice, or the 
CREATE TABLE statement won't have them, so you have to go back and forth 
in your text editor to see the comments.  Both solutions are suboptimal.

What I would prefer is something like this:

CREATE TABLE foo(  f1 int NOT NULL COMMENT    'the first field',  f2 int NOT NULL COMMENT    'the second field',
...
);

which would ensure the comments are both next to the field definition 
they're documenting and that they make it all the way to the database. 
I looked into the biggest products, and MySQL supports this syntax.  I 
couldn't find any similar syntax in any other product.

The downside is that this would require us to make COMMENT a fully 
reserved keyword, which would quite likely break at least one 
application out in the wild.  Another option would be to make the syntax 
something like  [ COLUMN COMMENT '...' ], but that's not exactly a 
beautiful solution either.

I still think this would be a really valuable feature if we can come up 
with a decent syntax for it.  Does anyone have any ideas?  Or does 
anybody want to shoot this proposal down right off the bat?


.m

On Sat, Jul 2, 2016 at 12:55 PM, Marko Tiikkaja <marko@joh.to> wrote:

What I would prefer is something like this:

CREATE TABLE foo(
  f1 int NOT NULL COMMENT
    'the first field',
  f2 int NOT NULL COMMENT
    'the second field',
...
);

which would ensure the comments are both next to the field definition they're documenting and that they make it all the way to the database. I looked into the biggest products, and MySQL supports this syntax.  I couldn't find any similar syntax in any other product.

​+1 for the idea - though restricting it to columns would not be ideal.

CREATE TABLE name 

COMMENT IS 

'Table Comment Here'

(

col1 serial COMMENT IS 'Place comment here'

)​;

On Sat, Jul 02, 2016 at 01:06:49PM -0400, David G. Johnston wrote:
> On Sat, Jul 2, 2016 at 12:55 PM, Marko Tiikkaja <marko@joh.to> wrote:
> 
> >
> > What I would prefer is something like this:
> >
> > CREATE TABLE foo(
> >   f1 int NOT NULL COMMENT
> >     'the first field',
> >   f2 int NOT NULL COMMENT
> >     'the second field',
> > ...
> > );
> >
> > which would ensure the comments are both next to the field definition
> > they're documenting and that they make it all the way to the database. I
> > looked into the biggest products, and MySQL supports this syntax.  I
> > couldn't find any similar syntax in any other product.
> >
> >
> ​+1 for the idea - though restricting it to columns would not be ideal.

+1 for adding it to all the CREATEs whose objects support COMMENT.

Might something like
   CREATE ... [WITH (COMMENT $$Big honking comment here$$)]

for the explicit CREATE cases and something like
   CREATE TABLE foo(       id SERIAL PRIMARY KEY WITH (COMMENT 'Generated primary key, best find a natural one, too'),
    t TEXT NOT NULL WITH (COMMENT 'Really?  A single-letter name?!?'),       ...   )
 

for cases where the CREATE isn't part of the syntax help alleviate the
keyword issue?

I suggested doing it this way because where there's one thing, in this
case a COMMENT, it's reasonable to expect that there will be others
and make that simpler to do.

Best,
David.
-- 
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

David Fetter <david@fetter.org> writes:
> On Sat, Jul 02, 2016 at 01:06:49PM -0400, David G. Johnston wrote:
>> ​+1 for the idea - though restricting it to columns would not be ideal.

> +1 for adding it to all the CREATEs whose objects support COMMENT.

TBH, I think this is a pretty bad idea.  I can see the reasoning for
allowing COMMENT in a table column definition, but the argument for
allowing it in simpler CREATEs seems tissue-thin:
CREATE FUNCTION foo(int) RETURNS ... ;COMMENT ON FUNCTION foo(int) IS 'blah';

vs
CREATE FUNCTION foo(int) RETURNS ...WITH (COMMENT 'blah');

Not much of a keystroke savings, nor is the comment noticeably
"closer" to its object than before.  Furthermore, the code footprint
of allowing that everywhere will be enormous.  And for statements that
already use WITH for something, I'm not sure you'll be able to
shoehorn this in without any grammatical trouble, either.  (It would
certainly be embarrassing if you did thirty-five flavors of CREATE
this way and then the syntax failed to work in the thirty-sixth.)

I think we should add something to ColumnDef and call it good.
        regards, tom lane

On 7/3/16 11:41 AM, Tom Lane wrote:
> I can see the reasoning for
> allowing COMMENT in a table column definition, but the argument for
> allowing it in simpler CREATEs seems tissue-thin:
> 
>     CREATE FUNCTION foo(int) RETURNS ... ;
>     COMMENT ON FUNCTION foo(int) IS 'blah';
> 
> vs
> 
>     CREATE FUNCTION foo(int) RETURNS ...
>     WITH (COMMENT 'blah');
> 
> Not much of a keystroke savings, nor is the comment noticeably
> "closer" to its object than before.

I had actually been thinking about a similar proposal, but specifically
for CREATE FUNCTION.  But the syntax would have to put it above the
function body, not below it.  I think the CREATE FUNCTION syntax could
actually handle that.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Fri, Aug 05, 2016 at 10:14:21AM -0400, Peter Eisentraut wrote:
> On 7/3/16 11:41 AM, Tom Lane wrote:
> > I can see the reasoning for
> > allowing COMMENT in a table column definition, but the argument for
> > allowing it in simpler CREATEs seems tissue-thin:
> > 
> >     CREATE FUNCTION foo(int) RETURNS ... ;
> >     COMMENT ON FUNCTION foo(int) IS 'blah';
> > 
> > vs
> > 
> >     CREATE FUNCTION foo(int) RETURNS ...
> >     WITH (COMMENT 'blah');
> > 
> > Not much of a keystroke savings, nor is the comment noticeably
> > "closer" to its object than before.
> 
> I had actually been thinking about a similar proposal, but specifically
> for CREATE FUNCTION.  But the syntax would have to put it above the
> function body, not below it.  I think the CREATE FUNCTION syntax could
> actually handle that.

For what it's worth, I tend to put the function body last.  That's
just my taste, though.  Would it be hard to keep the ability to
permute the stuff after
   CREATE FUNCTION (args)   RETURNS [SETOF] type

as we have it now?

Best,
David.
-- 
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On 8/5/16 11:58 AM, David Fetter wrote:
> For what it's worth, I tend to put the function body last.  That's
> just my taste, though.  Would it be hard to keep the ability to
> permute the stuff after
> 
>     CREATE FUNCTION (args)
>     RETURNS [SETOF] type
> 
> as we have it now?

I don't think anybody is suggesting to change that.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services