Dotaz: PostgreSQL: vypsat seznam DB určitého vlastníka

[Amarok]

Uz asi pul hodiny zkousim zjistit, jak vypsat seznam databazi, ktere patri urcitemu vlastnikovi. Pouzivam psql jako klienta.

Prikaz

\l

mi vypise vsechny DB, jenze na serveru jich je tolik, ze se v tom neda vyznat, chtel bych videt jen svoje DB.

Proste neco ve smyslu
select * from pg_database where owner='uzivatel';

Samozrejme v pg_database zadny sloupec owner neni, to je jen jako priklad. Nebo by to slo nejak pres ten prikaz \l s nejakym parametrem?

---

---

Odpovědi

[Jiří Hlinka]

Naucivam Vas trik, ktery ucim na svych skolenich - i kdyz se mozna pripravim o zakaznika :). Pokud spustite konzolu s parametrem -E, tak se Vam vypisi vsechny SQL prikazy pouzite pro realizaci metaprikazu:

[pavel@nemesis ~]$ psql -E postgres
Welcome to psql 8.3.9, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

postgres=# \l
********* QUERY **********
SELECT d.datname as "Name",
       r.rolname as "Owner",
       pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding"
FROM pg_catalog.pg_database d
  JOIN pg_catalog.pg_roles r ON d.datdba = r.oid
ORDER BY 1;
**************************

        List of databases
   Name    |  Owner   | Encoding 
-----------+----------+----------
 foo       | pavel    | UTF8
 postgres  | postgres | UTF8
 template0 | postgres | UTF8
 template1 | postgres | UTF8
(4 rows)

Pak staci jen zkopirovat dotaz a upravit:

postgres=# SELECT d.datname as "Name",
       r.rolname as "Owner",
       pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding"
FROM pg_catalog.pg_database d
  JOIN pg_catalog.pg_roles r ON d.datdba = r.oid
 WHERE r.rolname = 'pavel' --<<<
ORDER BY 1;
 Name | Owner | Encoding 
------+-------+----------
 foo  | pavel | UTF8
(1 row)

[Amarok]

Tak na tohle bych jentak neprisel :) Dik. Funguje.

[Ivan]

Tak za toto Vam velmi pekne dakujem. Je to sice v man, ale nikdy by ma to nenapadlo pouzit.