From: Guy Fraser <guy@incentre.net>
To: pgsql-sql@postgresql.org
Subject: Re: SQL to list databases?
Date: Fri, 24 Jan 2003 11:26:27 -0700
Message-ID: <3E318553.7050101@incentre.net> (raw)
References: <81132473206F3A46A72BD6116E1A06AE1B14CC@black.aprote.com>
<26610.1043341252@sss.pgh.pa.us>
<3E303AF2.10603@cms-stl.com>
<60030000.1043353831@lerlaptop.iadfw.net>
Hi
To make it easier to do this in SQL you can create a view like this :
CREATE VIEW db_list AS
SELECT d.datname as "Name",
u.usename as "Owner",
pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding"
FROM pg_catalog.pg_database d
LEFT JOIN pg_catalog.pg_user u ON d.datdba = u.usesysid
ORDER BY 1;
Note: the "select" statement comes from the post I am replying from.
Then all you have to do is :
select * from db_list;
For example this is my output :
foobar=# select * from db_list;
Name | Owner | Encoding
-----------+-------+-----------
foobar | turk | SQL_ASCII
template0 | pgsql | SQL_ASCII
template1 | pgsql | SQL_ASCII
(3 rows)
Or :
foobar=# select "Name","Owner" from db_list where "Owner" != 'pgsql';
Name | Owner
--------+-------
foobar | turk
(1 row)
Using psql -E {database} interactivly
Or
psql -E -c "\{command}" {database}
Example:
user@host:~$ psql -E -c "\dt" template1
********* QUERY **********
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN
'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
u.usename as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************
You can collect the SQL for other helpful commands and build views like above,
then you can query the view for more specific information.
I hope this is helpful.
Guy
PS: If you create these "views" in template1 before you create your other
databases, these views will be included in new databases automaticaly.
Larry Rosenman wrote:
>
>
> --On Thursday, January 23, 2003 12:56:50 -0600 Ben Siders
> <bsiders@cms-stl.com> wrote:
>
>>
>> Is there a query that will return all the databases available, similar to
>> what psql -l does?
>>
> $ psql -E -l
> ********* QUERY **********
> SELECT d.datname as "Name",
> u.usename as "Owner",
> pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding"
> FROM pg_catalog.pg_database d
> LEFT JOIN pg_catalog.pg_user u ON d.datdba = u.usesysid
> ORDER BY 1;
> **************************
>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 6: Have you searched our list archives?
>>
>> http://archives.postgresql.org
>>
>
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: guy@incentre.net
Subject: Re: SQL to list databases?
In-Reply-To: <3E318553.7050101@incentre.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox