Received: from athene.i.eunet.no (athene.i.eunet.no [193.71.2.52]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f63CZYa40281 for ; Tue, 3 Jul 2001 08:35:34 -0400 (EDT) (envelope-from tih@athene.i.eunet.no) Received: by athene.i.eunet.no (Postfix, from userid 1001) id CE9A0240F; Tue, 3 Jul 2001 14:35:28 +0200 (CEST) To: Vince Vielhaber Cc: Roman Smirnov , PostgreSQL conferention Subject: Re: SQL query: List all the databases in the server References: From: Tom Ivar Helbekkmo Date: 03 Jul 2001 14:35:28 +0200 In-Reply-To: (Vince Vielhaber's message of "Tue, 3 Jul 2001 07:44:19 -0400 (EDT)") Message-ID: <86zoamdwbz.fsf@athene.i.eunet.no> Lines: 43 User-Agent: Gnus/5.090004 (Oort Gnus v0.04) Emacs/20.7 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Archive-Number: 200107/6 X-Sequence-Number: 855 Vince Vielhaber 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.