Re: How to list databases with SQL statement?

Hello,
Is there a way to list all databases which belong to the current user with an
SQL query?

Regards M.Marinov
--
One Planet, One Internet.
We Are All Connected.

On Saturday 20 November 2004 02:39, Marian D Marinov wrote:
> Hello,
> Is there a way to list all databases which belong to the current user with
> an SQL query?

I am not sure about a SQL Query.

But if you login to the database using the command line psql interface and run

\l

it will give you output similar to this:

intsys=> \l
List of databases
Name | Owner | Encoding
-----------+----------+-----------
intsys | intsys | SQL_ASCII
template0 | postgres | SQL_ASCII
template1 | postgres | SQL_ASCII
(3 rows)

intsys=>

Not sure if this will help you.

> Regards M.Marinov

Q
--
Quinton Delpeche
Internal Systems Developer
Softline VIP

Telephone: +27 12 420 7000
Direct: +27 12 420 7007
Facsimile: +27 12 420 7344

http://www.vippayroll.co.za/

Anarchy may not be the best form of government, but it's better than no
government at all.

On Sat, Nov 20, 2004 at 12:39:38AM +0000, Marian D Marinov wrote:

> Is there a way to list all databases which belong to the current user with an
> SQL query?

Such information is in the system catalogs:

http://www.postgresql.org/docs/7.4/static/catalogs.html

If you run psql with the -E option, you can see the queries that
psql makes when you issue commands like \l to show the list of
databases and their owners. You can then copy those queries and
modify them to meet your needs.

The name of the current user is available as CURRENT_USER and
SESSION_USER -- see the Miscellaneous Functions documentation for
the difference:

http://www.postgresql.org/docs/7.4/static/functions-misc.html

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

On Nov 19, 2004, at 7:39 PM, Marian D Marinov wrote:

> Hello,
> Is there a way to list all databases which belong to the current user
> with an
> SQL query?

select datname, usename
from pg_catalog.pg_database, pg_catalog.pg_user
where datdba = usesysid and usename = current_user;

Best,

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL