Обсуждение: How to retrieve Comment text using SQL, not psql?
Using pgAdmin3 I've tried this and variations on it. All are rejected.
select COMMENT ON TABLE articlestats
No answer here,
pgAdmin3 had no problem with entering a comment:
COMMENT ON TABLE articlestats IS 'Comprehensive data for every article.'
- Bob Futrelle
Hi
you can call function obj_description http://stackoverflow.com/questions/11493978/how-to-retrieve-the-comment-of-a-postgresql-databasehttp://www.postgresql.org/docs/9.1/static/functions-info.html
2015-05-30 13:48 GMT+02:00 Bob Futrelle <bob.futrelle@gmail.com>:
Using pgAdmin3 I've tried this and variations on it. All are rejected.select COMMENT ON TABLE articlestatsNo answer here,pgAdmin3 had no problem with entering a comment:COMMENT ON TABLE articlestats IS 'Comprehensive data for every article.'- Bob Futrelle
This will give the comment on your table and any column:
SELECT DISTINCT ON (c.relname)
n.nspname as schema,
c.relname,
a.rolname as owner,
0 as col_seq,
'' as column,
d.description as comment
FROM pg_class c
LEFT JOIN pg_attribute col ON (col.attrelid = c.oid)
LEFT JOIN pg_description d ON (d.objoid = col.attrelid AND d.objsubid = 0)
JOIN pg_namespace n ON (n.oid = c.relnamespace)
JOIN pg_authid a ON ( a.OID = c.relowner )
WHERE n.nspname NOT LIKE 'information%'
AND relname NOT LIKE 'pg_%'
AND relname NOT LIKE 'information%'
AND relname NOT LIKE 'sql_%'
AND relname = 'articlestats'
AND relkind = 'r'
AND d.description IS NOT NULL
UNION
SELECT n.nspname as schema,
c.relname,
'' as owner,
col.attnum as col_seq,
col.attname as column,
d.description
FROM pg_class c
JOIN pg_attribute col ON (col.attrelid = c.oid)
LEFT JOIN pg_description d ON (d.objoid = col.attrelid AND d.objsubid = col.attnum)
JOIN pg_namespace n ON (n.oid = c.relnamespace)
JOIN pg_authid a ON ( a.OID = c.relowner )
WHERE n.nspname NOT LIKE 'information%'
AND relname NOT LIKE 'pg_%'
AND relname NOT LIKE 'information%'
AND relname NOT LIKE 'sql_%'
AND relname = 'articlestats'
AND relkind = 'r'
AND d.description IS NOT NULL
AND col.attnum >= 0
ORDER BY 1, 2, 4;
Learn the catalogs and you will learn PostgreSQL!SELECT DISTINCT ON (c.relname)
n.nspname as schema,
c.relname,
a.rolname as owner,
0 as col_seq,
'' as column,
d.description as comment
FROM pg_class c
LEFT JOIN pg_attribute col ON (col.attrelid = c.oid)
LEFT JOIN pg_description d ON (d.objoid = col.attrelid AND d.objsubid = 0)
JOIN pg_namespace n ON (n.oid = c.relnamespace)
JOIN pg_authid a ON ( a.OID = c.relowner )
WHERE n.nspname NOT LIKE 'information%'
AND relname NOT LIKE 'pg_%'
AND relname NOT LIKE 'information%'
AND relname NOT LIKE 'sql_%'
AND relname = 'articlestats'
AND relkind = 'r'
AND d.description IS NOT NULL
UNION
SELECT n.nspname as schema,
c.relname,
'' as owner,
col.attnum as col_seq,
col.attname as column,
d.description
FROM pg_class c
JOIN pg_attribute col ON (col.attrelid = c.oid)
LEFT JOIN pg_description d ON (d.objoid = col.attrelid AND d.objsubid = col.attnum)
JOIN pg_namespace n ON (n.oid = c.relnamespace)
JOIN pg_authid a ON ( a.OID = c.relowner )
WHERE n.nspname NOT LIKE 'information%'
AND relname NOT LIKE 'pg_%'
AND relname NOT LIKE 'information%'
AND relname NOT LIKE 'sql_%'
AND relname = 'articlestats'
AND relkind = 'r'
AND d.description IS NOT NULL
AND col.attnum >= 0
ORDER BY 1, 2, 4;
On Sat, May 30, 2015 at 7:58 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
Pavel StehuleRegardsSELECT pg_catalog.obj_description('tablename'::regclass, 'pg_class') as "Description;For tablesHiyou can call function obj_description http://stackoverflow.com/questions/11493978/how-to-retrieve-the-comment-of-a-postgresql-database
http://www.postgresql.org/docs/9.1/static/functions-info.html2015-05-30 13:48 GMT+02:00 Bob Futrelle <bob.futrelle@gmail.com>:Using pgAdmin3 I've tried this and variations on it. All are rejected.select COMMENT ON TABLE articlestatsNo answer here,pgAdmin3 had no problem with entering a comment:COMMENT ON TABLE articlestats IS 'Comprehensive data for every article.'- Bob Futrelle
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
On 05/30/2015 04:48 AM, Bob Futrelle wrote: > Using pgAdmin3 I've tried this and variations on it. All are rejected. > > select COMMENT ON TABLE articlestats > > > No answer here, > > http://www.postgresql.org/docs/9.3/static/sql-comment.html > > > pgAdmin3 had no problem with entering a comment: > > COMMENT ON TABLE articlestats IS 'Comprehensive data for every article.' http://www.postgresql.org/docs/9.4/interactive/functions-info.html Table 9.60 Comment Information Functions So: test=# comment on table table1 is 'Test comment'; COMMENT test=# select obj_description('table1'::regclass, 'pg_class'); obj_description ----------------- Test comment (1 row) > > > - Bob Futrelle > -- Adrian Klaver adrian.klaver@aklaver.com
>select obj_description('table1'::regclass, 'pg_class');
That will only show the table comment.
On Sat, May 30, 2015 at 9:37 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
http://www.postgresql.org/docs/9.4/interactive/functions-info.htmlOn 05/30/2015 04:48 AM, Bob Futrelle wrote:Using pgAdmin3 I've tried this and variations on it. All are rejected.
select COMMENT ON TABLE articlestats
No answer here,
http://www.postgresql.org/docs/9.3/static/sql-comment.html
pgAdmin3 had no problem with entering a comment:
COMMENT ON TABLE articlestats IS 'Comprehensive data for every article.'
Table 9.60 Comment Information Functions
So:
test=# comment on table table1 is 'Test comment';
COMMENT
test=# select obj_description('table1'::regclass, 'pg_class');
obj_description
-----------------
Test comment
(1 row)
- Bob Futrelle
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
Hi Bob: On Sat, May 30, 2015 at 1:48 PM, Bob Futrelle <bob.futrelle@gmail.com> wrote: > Using pgAdmin3 I've tried this and variations on it. All are rejected. > select COMMENT ON TABLE articlestats pgAdmin3 is a bit complex for me, bot on vanilla psql you can use the switch: >>> -E --echo-hidden Echo the actual queries generated by \d and other backslash commands. You can use this to study psql's internal operations. This is equivalent to setting the variable ECHO_HIDDEN to on. <<< to learn how it does them, and elaborate from there, Its a nice resource ( although I fear its queries evolve with versions, but you can always port them touse the information_schema ( http://www.postgresql.org/docs/9.4/static/information-schema.html ) which should be a little more stable. Francisco Olarte.