Thread: 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 mightand 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