From: Josh Kupershmidt [mailto:schmiddy(at)gmail(dot)com]
Sent: Monday, August 16, 2010 8:51 AM
To: Chris Campbell
Subject: Re: [NOVICE] Listing Schemas - Revisited
On Tue, Aug 10, 2010 at 1:42 PM, Chris Campbell <ccampbell(at)cascadeds(dot)com> wrote:
> Hi, a while back I saw a thread about how to list schemas. The solution
> presented was:
> Select * FROM pg_namespace
> That's great except the results include non schema data and no real way
> (that I can tell) to differentiate the actual schemas from the other data.
> In addition, I need to include the schema comments. So to summarize my
> particular need: I need to issue a select statement that returns "Schema
> Name" and "Comments" for the given database.
Hrm, what sort of "other data" do you see in pg_namespace? And how about this:
SELECT nspname AS "Schema Name", pg_catalog.obj_description(oid) AS "Comments"
WHERE nspname !~ '^pg_.*';
Hi Josh, thanks so much for your reply.
Initially my pg_namespace query listing contains the following records: (sorry for the poor formatting)
Your solution is exactly what I'm looking for.
My database contains two schemas: Public and dd1 (and possible more). The comments associated with the schemas are listing beautifully. Thank you for that. However when I run the query I now end up with one extra, unwanted record called "information_schema". Can I assume that 'information_schema' is a system record and is consistent across all pg databases that I create in the future? If so I'll add it to the filter and I end up with "exactly" what I'm looking for. Thanks again.
In response to
pgsql-novice by date
|Next:||From: Leon Starr||Date: 2010-08-16 17:13:33|
|Subject: Re: How to trap invalid enum input exception? |
|Previous:||From: Josh Kupershmidt||Date: 2010-08-16 15:50:55|
|Subject: Re: Listing Schemas - Revisited|