Message-ID: <3E318553.7050101@incentre.net>
Date: Fri, 24 Jan 2003 11:26:27 -0700
From: Guy Fraser <guy@incentre.net>
Organization: J I Reasearch
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.0.1) Gecko/20021003
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: SQL to list databases?
References: <81132473206F3A46A72BD6116E1A06AE1B14CC@black.aprote.com>
	<26610.1043341252@sss.pgh.pa.us> <3E303AF2.10603@cms-stl.com>
	<60030000.1043353831@lerlaptop.iadfw.net>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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
>>
> 
> 
>