Обсуждение: Listing Schemas - Revisited
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.
Thanks,
Chris Campbell
Cascade Data Solutions, Inc.
ccampbell@CascadeDS.com
On Tue, Aug 10, 2010 at 1:42 PM, Chris Campbell <ccampbell@cascadeds.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
-----Original Message----- From: Josh Kupershmidt [mailto:schmiddy@gmail.com] Sent: Monday, August 16, 2010 8:51 AM To: Chris Campbell Cc: pgsql-novice@postgresql.org Subject: Re: [NOVICE] Listing Schemas - Revisited On Tue, Aug 10, 2010 at 1:42 PM, Chris Campbell <ccampbell@cascadeds.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 listingbeautifully. 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 databasesthat 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