Thread: How to retrieve Comment text using SQL, not psql?
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.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
>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.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.