Обсуждение: comment doesn't accept expressions returning text

I've been trying to be better at documentation in general and have  
been trying to take advantage of PostgreSQL's COMMENT ON  
functionality to provide a little more information in the DDL itself.  
I usually write my DDL in a text file and load it into the database  
using psql. To make it (a little) easier to write comments, I'd like  
to write the comment text as it's own paragraph so I don't have to  
worry about accidently deleting the opening and closing quotes.

For example:

create table foo (foo_id integer primary key);
comment on table foo is $$
This is a comment for table foo.
$$;

Of course, this comment includes a new line at the beginning and end  
of the comment.

test=# select relname, description
test-# from pg_description
test-# join pg_class on (pg_class.oid = pg_description.objoid)
test-# where relname = 'foo';
relname | description
---------+-------------
foo     |
This is a comment for table foo.

(1 row)

It would be nice to be able to strip those out using TRIM (or some  
other function). However, this doesn't appear to work, as COMMENT ON  
throws a syntax error as soon as it runs into anything that isn't a  
pure text string. Examples below.

Would there be any objection to allowing any text-returning  
expression in this case? If not, what would be involved in allowing  
this? I'm interested in contributing the change if it's something  
that's considered worthwhile.

Michael Glaesemann
grzm myrealbox com


test=# select version();                                                                    
version
------------------------------------------------------------------------ 
----------------------------------------------------------------------
PostgreSQL 8.1.0 on powerpc-apple-darwin8.3.0, compiled by GCC  
powerpc-apple-darwin8-gcc-4.0.0 (GCC) 4.0.0 (Apple Computer, Inc.  
build 5026)
(1 row)

test=# create table foo (foo_id integer primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index  
"foo_pkey" for table "foo"
CREATE TABLE
test=# comment on table foo is trim('*', '***This is just an  
example***');
ERROR:  syntax error at or near "trim" at character 25
LINE 1: comment on table foo is trim('*', '***This is just an exampl...                                ^
test=# comment on table foo is (trim('*', '***This is just an  
example***'));
ERROR:  syntax error at or near "(" at character 25
LINE 1: comment on table foo is (trim('*', '***This is just an examp...                                ^
test=# comment on table foo is 'This is just' || ' an example';
ERROR:  syntax error at or near "||" at character 40
LINE 1: comment on table foo is 'This is just' || ' an example';                                               ^
test=# comment on table foo is ('This is just' || ' an example');
ERROR:  syntax error at or near "(" at character 25
LINE 1: comment on table foo is ('This is just' || ' an example');

To change this would involve changing the grammar.

You could possibly write a stored proc that manipulated pg_description
directly.
cheers

andrew

Michael Glaesemann said:
> I've been trying to be better at documentation in general and have
> been trying to take advantage of PostgreSQL's COMMENT ON
> functionality to provide a little more information in the DDL itself.
> I usually write my DDL in a text file and load it into the database
> using psql. To make it (a little) easier to write comments, I'd like
> to write the comment text as it's own paragraph so I don't have to
> worry about accidently deleting the opening and closing quotes.
>
> For example:
>
> create table foo (foo_id integer primary key);
> comment on table foo is $$
> This is a comment for table foo.
> $$;
>
> Of course, this comment includes a new line at the beginning and end
> of the comment.
>
> test=# select relname, description
> test-# from pg_description
> test-# join pg_class on (pg_class.oid = pg_description.objoid)
> test-# where relname = 'foo';
> relname | description
> ---------+-------------
> foo     |
> This is a comment for table foo.
>
> (1 row)
>
> It would be nice to be able to strip those out using TRIM (or some
> other function). However, this doesn't appear to work, as COMMENT ON
> throws a syntax error as soon as it runs into anything that isn't a
> pure text string. Examples below.
>
> Would there be any objection to allowing any text-returning
> expression in this case? If not, what would be involved in allowing
> this? I'm interested in contributing the change if it's something
> that's considered worthwhile.
>
> Michael Glaesemann
> grzm myrealbox com
>
>
> test=# select version();
>
> version
> ------------------------------------------------------------------------
>  ----------------------------------------------------------------------
> PostgreSQL 8.1.0 on powerpc-apple-darwin8.3.0, compiled by GCC
> powerpc-apple-darwin8-gcc-4.0.0 (GCC) 4.0.0 (Apple Computer, Inc.
> build 5026)
> (1 row)
>
> test=# create table foo (foo_id integer primary key);
> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
> "foo_pkey" for table "foo"
> CREATE TABLE
> test=# comment on table foo is trim('*', '***This is just an
> example***');
> ERROR:  syntax error at or near "trim" at character 25
> LINE 1: comment on table foo is trim('*', '***This is just an exampl...
>                                 ^
> test=# comment on table foo is (trim('*', '***This is just an
> example***'));
> ERROR:  syntax error at or near "(" at character 25
> LINE 1: comment on table foo is (trim('*', '***This is just an examp...
>                                 ^
> test=# comment on table foo is 'This is just' || ' an example';
> ERROR:  syntax error at or near "||" at character 40
> LINE 1: comment on table foo is 'This is just' || ' an example';
>                                                ^
> test=# comment on table foo is ('This is just' || ' an example');
> ERROR:  syntax error at or near "(" at character 25
> LINE 1: comment on table foo is ('This is just' || ' an example');
>
>
> ---------------------------(end of
> broadcast)--------------------------- TIP 3: Have you checked our
> extensive FAQ?
>
>               http://www.postgresql.org/docs/faq

Thanks for the feedback, Andrew.

On Nov 24, 2005, at 12:02 , Andrew Dunstan wrote:

> To change this would involve changing the grammar.

I thought it might—and am thinking it might be all that would need
changing.

> You could possibly write a stored proc that manipulated pg_description
> directly.

I thought about that, and thought that I'd rather make a more general
fix if there weren't any objections to allowing COMMENT ON to accept
any text input (rather than just a string). Given the option, I'd
rather not have to carry around a function to do this.

Michael Glaesemann
grzm myrealbox com

Michael Glaesemann said:
>
> On Nov 24, 2005, at 12:02 , Andrew Dunstan wrote:
>
>> To change this would involve changing the grammar.
>
> I thought it might—and am thinking it might be all that would need
> changing.
>


No, you'd have to have code to get the expression evaluated.

cheers

andrew

On Nov 24, 2005, at 12:29 , Andrew Dunstan wrote:

> Michael Glaesemann said:
>>
>> On Nov 24, 2005, at 12:02 , Andrew Dunstan wrote:
>>
>>> To change this would involve changing the grammar.
>>
>> I thought it might—and am thinking it might be all that would need
>> changing.
>>
>
>
> No, you'd have to have code to get the expression evaluated.

Cool. I'll do some digging.

Michael Glaesemann
grzm myrealbox com

Added to TODO:
* Allow COMMENT ON to accept an expression rather than just a   string


---------------------------------------------------------------------------

Michael Glaesemann wrote:
> I've been trying to be better at documentation in general and have  
> been trying to take advantage of PostgreSQL's COMMENT ON  
> functionality to provide a little more information in the DDL itself.  
> I usually write my DDL in a text file and load it into the database  
> using psql. To make it (a little) easier to write comments, I'd like  
> to write the comment text as it's own paragraph so I don't have to  
> worry about accidently deleting the opening and closing quotes.
> 
> For example:
> 
> create table foo (foo_id integer primary key);
> comment on table foo is $$
> This is a comment for table foo.
> $$;
> 
> Of course, this comment includes a new line at the beginning and end  
> of the comment.
> 
> test=# select relname, description
> test-# from pg_description
> test-# join pg_class on (pg_class.oid = pg_description.objoid)
> test-# where relname = 'foo';
> relname | description
> ---------+-------------
> foo     |
> This is a comment for table foo.
> 
> (1 row)
> 
> It would be nice to be able to strip those out using TRIM (or some  
> other function). However, this doesn't appear to work, as COMMENT ON  
> throws a syntax error as soon as it runs into anything that isn't a  
> pure text string. Examples below.
> 
> Would there be any objection to allowing any text-returning  
> expression in this case? If not, what would be involved in allowing  
> this? I'm interested in contributing the change if it's something  
> that's considered worthwhile.
> 
> Michael Glaesemann
> grzm myrealbox com
> 
> 
> test=# select version();
>                                                                      
> version
> ------------------------------------------------------------------------ 
> ----------------------------------------------------------------------
> PostgreSQL 8.1.0 on powerpc-apple-darwin8.3.0, compiled by GCC  
> powerpc-apple-darwin8-gcc-4.0.0 (GCC) 4.0.0 (Apple Computer, Inc.  
> build 5026)
> (1 row)
> 
> test=# create table foo (foo_id integer primary key);
> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index  
> "foo_pkey" for table "foo"
> CREATE TABLE
> test=# comment on table foo is trim('*', '***This is just an  
> example***');
> ERROR:  syntax error at or near "trim" at character 25
> LINE 1: comment on table foo is trim('*', '***This is just an exampl...
>                                  ^
> test=# comment on table foo is (trim('*', '***This is just an  
> example***'));
> ERROR:  syntax error at or near "(" at character 25
> LINE 1: comment on table foo is (trim('*', '***This is just an examp...
>                                  ^
> test=# comment on table foo is 'This is just' || ' an example';
> ERROR:  syntax error at or near "||" at character 40
> LINE 1: comment on table foo is 'This is just' || ' an example';
>                                                 ^
> test=# comment on table foo is ('This is just' || ' an example');
> ERROR:  syntax error at or near "(" at character 25
> LINE 1: comment on table foo is ('This is just' || ' an example');
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faq
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073