From: | Tom Ivar Helbekkmo <tih(at)kpnQwest(dot)no> |
---|---|
To: | Vince Vielhaber <vev(at)michvhf(dot)com> |
Cc: | Roman Smirnov <smirnov(at)dresearch(dot)de>, PostgreSQL conferention <pgsql-docs(at)postgresql(dot)org> |
Subject: | Re: SQL query: List all the databases in the server |
Date: | 2001-07-03 12:35:28 |
Message-ID: | 86zoamdwbz.fsf@athene.i.eunet.no |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-docs |
Vince Vielhaber <vev(at)michvhf(dot)com> writes:
>> I need SQL analog of \l command from psql.
>> Something like "list databases".
>
> If you just want a list of them you can get it from pg_database:
>
> select datname from pg_database;
>
> if you also want the username of the database owner [...]
The psql program implements the various \-ed information commands
using SQL, and you can find the actual code by perusing its source
file "describe.c". In this case, we find that "\l" is:
SELECT pg_database.datname as "Database",
pg_user.usename as "Owner",
pg_encoding_to_char(pg_database.encoding) as "Encoding",
obj_description(pg_database.oid) as "Description"
FROM pg_database, pg_user
WHERE pg_database.datdba = pg_user.usesysid
UNION
SELECT pg_database.datname as "Database",
NULL as "Owner",
pg_encoding_to_char(pg_database.encoding) as "Encoding",
obj_description(pg_database.oid) as "Description"
FROM pg_database
WHERE pg_database.datdba NOT IN (SELECT usesysid FROM pg_user)
ORDER BY "Database";
However, the "Encoding" bits are only included if the system is
compiled with support for multiple character set encodings, and the
"Description" bits only if the command is given as "\l+", which is a
new one for me -- it's not included in "\?" output. It seems, from a
little experimentation, that that "+" suffix is available also for the
other "\" commands where it's relevant. Cool! :-)
The above SELECT is extensively reformatted from the strings it's
built from in the source file, of course.
-tih
--
The basic difference is this: hackers build things, crackers break them.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2001-07-03 14:45:49 | Re: SQL query: List all the databases in the server |
Previous Message | Justin Clift | 2001-07-03 12:29:30 | Re: replication |