0

I'm writing a script that's supposed to dump SQL object definitions in a certain format, and I'm looking for a way to get the schema comment programmatically.

What SQL query do I need to run if I know what the schema's name is?

I'm currently on PostgreSQL 9.4.

Primordial_Hamster
  • 53
  • 1
  • 7
  • Does this answer your question? [How to retrieve the comment of a PostgreSQL database?](https://stackoverflow.com/questions/11493978/how-to-retrieve-the-comment-of-a-postgresql-database) – Simon Martinelli Mar 14 '21 at 18:24
  • @SimonMartinelli No, because it shows how to get the comment for a table or a database. I tried to modify the queries in that answer to get a schema comment, but without success. – Primordial_Hamster Mar 14 '21 at 18:34
  • Unrelated to your problem, but: Postgres 9.4 is [no longer supported](https://www.postgresql.org/support/versioning/) you should plan an upgrade as soon as possible. –  Mar 14 '21 at 20:01
  • @a_horse_with_no_name Yes we're working on that... – Primordial_Hamster Mar 15 '21 at 08:57

1 Answers1

1

You can use obj_description() for that:

comment on schema some_schema is 'Some comment';
select obj_description('some_schema'::regnamespace);

Or to get it from all schemas:

select nspname, obj_description(oid, 'pg_namespace')
from pg_namespace;
  • The second one worked for me, the first one doesn't run because it doesn't know what `regnamespace` is. – Primordial_Hamster Mar 15 '21 at 09:00
  • @Primordial_Hamster: that's because you are using an outdated Postgres version. It works with all supported Postgres versions. –  Mar 15 '21 at 09:08