Skip site navigation (1) Skip section navigation (2)

Re: Listing Schemas - Revisited

From: Chris Campbell <ccampbell(at)cascadeds(dot)com>
To: Josh Kupershmidt <schmiddy(at)gmail(dot)com>
Cc: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Listing Schemas - Revisited
Date: 2010-08-16 16:15:48
Message-ID: 453A24085F801842AEA8D0B6B269065D2FECF16896@HDMC.cds.local (view raw or flat)
Thread:
Lists: pgsql-novice
-----Original Message-----
From: Josh Kupershmidt [mailto:schmiddy(at)gmail(dot)com] 
Sent: Monday, August 16, 2010 8:51 AM
To: Chris Campbell
Cc: pgsql-novice(at)postgresql(dot)org
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"
FROM pg_namespace
WHERE nspname !~ '^pg_.*';

Josh


Hi Josh, thanks so much for your reply.

Initially my pg_namespace query listing contains the following records:  (sorry for the poor formatting)

11;"pg_catalog";10;"{postgres=UC/postgres,=U/postgres}"
99;"pg_toast";10;""
2200;"public";10;"{postgres=UC/postgres,=UC/postgres}"
11061;"pg_temp_1";10;""
11062;"pg_toast_temp_1";10;""
11326;"information_schema";10;"{postgres=UC/postgres,=U/postgres}"
327722;"dd1";16469;""

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.

- CBC







In response to

pgsql-novice by date

Next:From: Leon StarrDate: 2010-08-16 17:13:33
Subject: Re: How to trap invalid enum input exception?
Previous:From: Josh KupershmidtDate: 2010-08-16 15:50:55
Subject: Re: Listing Schemas - Revisited

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group