Re: SQL to list databases? - Mailing list pgsql-sql

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