Re: SQL to list databases?

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(at)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(at)cms-stl(dot)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
>>
>
>
>