PostgreSqlシステムカタログを組み合わせて

質問

PostgreSqlシステムカタログを組み合わせて

データベース名 スキーマ名 テーブル名
----------- -------- --------
dtabase1 public table101
dtabase1 schema1 table111
dtabase1 schema1 table112
dtabase2 public table211
dtabase3 schema1 table311
dtabase3 schema2 table321

というようなデータを取ることは出来ますか？

回答No.2

yamada59 · ベストアンサー

簡単には実現できないです。

データベースの一覧は pg_database を参照すれば取得できますが、スキーマとテーブルの一覧は接続中のデータベース内のものしか取得できません。

接続中のデータベース内のスキーマとテーブルの一覧であれば以下の SQL で取得できます。

SELECT n.nspname, c.relname
FROM pg_namespace AS n JOIN pg_class AS c ON (n.oid = c.relnamespace)
WHERE c.relkind = 'r';

どうしても別のデータベース内のスキーマとテーブルの一覧を取得したい場合には、dblink などのモジュールを使用する必要があります。

ただ、dblink を使用しても PL/pgSQL 関数などを作成せずに 1 つの SQL で実現するのは困難です (私の技術がないだけかもしれませんが)。

以下の SQL でデータベースの一覧を取得し、各データベースに接続してスキーマとテーブルの一覧を取得するのが簡単だと思います。

SELECT datname FROM pg_database WHERE datallowconn;

お礼

ありがとうございます。

やはり簡単には出来ないですか。
それが分かっただけでも収穫ですが、さらに補助的な情報を頂けたので、後はそれを参考にさせて頂いて、どうするか考えたいと思います。

回答No.1

chukenkenkou

まず、前置きです。

各RDBMSにおいて、表、列などの定義情報の管理方法は様々で、呼び名もカタログ、ディクショナリなど様々です。また、定義情報の参照方法も、SQLで通常の表の検索のように行えるものもあれば、特別なコマンドを使ったりと様々です。

こういった「RDBMS毎に定義情報の参照方法が違う」といったことを改善するため、標準SQLで規定されたのが情報スキーマ（information_schrema）です。

例えば、標準SQL準拠で情報スキーマを実装しているRDBMSでは、

表名一覧など・・・データベース名やスキーマ名などを条件に、tables表を検索
表の列名一覧など・・・データベース名やスキーマ名などに加え、表名を条件にcolumns表を検索

といった方法で得られます。

ただし、表名や列名など、どのRDBMSでも同じ概念のものはいいのですが、データベース、スキーマなどRDBMSごとに概念が違うものもあり、そういった場合は、どの列にどの情報が入っているか確認する必要があります。

さて、表名一覧ですが、上記のように、

SELECT * FROM information_schema.tables
WHERE ・・・
ORDER BY ・・・

といったSQLで得られると思います。

参考URL：

http://www.postgresql.jp/document/pg746doc/html/infoschema-tables.html

お礼

ありがとうございます。

解説頂いた事は大体理解しております。
tablesにスキーマとテーブルが紐付く情報が入っているのも知っていました。
しかし、データベース内のローカル情報ですので、
そこらから私の希望している「データベース名×スキーマ名×テーブル名」の情報を導き出すのが困難です。

pg_databaseにデータベースの一覧があります。
これに紐付ける事ができれば良いのですが、視野が違うために

データベース名 スキーマ名 テーブル名
----------- -------- --------
dtabase1 public table101
dtabase1 schema1 table111
dtabase1 schema1 table112
dtabase2 public table211
dtabase3 schema1 table311
dtabase3 schema2 table321

こういう形で求めるのは難しいのです。
何か良い手段はありませんか？