ユーザー・スキーマ・テーブル・カラム

テーブルのカラム名とデータ型を、SQLを使って動的に取得する。 - 永字八法の続き。面倒なので、VIEWにしてしまおうと思う。

SELECT pg_user.usename AS "owner", pg_namespace.nspname AS "schema", pg_class.relname AS "table", pg_attribute.attname AS "column", pg_type.typname AS "type", pg_attribute.attlen AS length
   FROM pg_namespace, pg_class, pg_attribute, pg_type, pg_user
  WHERE pg_user.usesysid = pg_namespace.nspowner AND pg_namespace.oid = pg_class.relnamespace AND pg_class.relkind = 'r'::"char" AND pg_class.oid = pg_attribute.attrelid AND pg_attribute.atttypid = pg_type.oid AND (pg_attribute.atttypid < 26::oid OR pg_attribute.atttypid > 29::oid)
  ORDER BY pg_namespace.nspowner, pg_namespace.nspname, pg_class.relname;

上記のSQLをVIEWにすると幸せかも。