- 2008年1月 5日 22:40
- データベース
PostgreSQLで、フィールド一覧を取得する方法です。
TABLEのみが対象になります。
フィールドサイズの取得は、面倒なのですがフィールドタイプで固定な数値を割り振りました。
「@TABLE_NAME」は、バインド変数なので、対象としたいテーブル名を入れてください。2箇所あるので注意してください。
SELECT A.FIELD_NAME, A.FIELD_TYPE, CASE WHEN A.FIELD_SIZE IS NULL THEN 0 ELSE A.FIELD_SIZE END AS FIELD_SIZE, CASE WHEN B.IS_PK IS NULL THEN 0 ELSE B.IS_PK END AS IS_PK, A.IS_NULLABLE FROM ( SELECT PG_CLASS.RELNAME AS TABLE_NAME, PG_ATTRIBUTE.ATTNUM AS FIELD_NUM, PG_ATTRIBUTE.ATTNAME AS FIELD_NAME, PG_TYPE.TYPNAME AS FIELD_TYPE, CASE WHEN PG_TYPE.TYPNAME = 'bpchar' THEN PG_ATTRIBUTE.ATTTYPMOD-4 WHEN PG_TYPE.TYPNAME = 'varchar' THEN PG_ATTRIBUTE.ATTTYPMOD-4 WHEN PG_TYPE.TYPNAME = 'numeric' THEN ( PG_ATTRIBUTE.ATTTYPMOD-4 ) / 65536 WHEN PG_TYPE.TYPNAME = 'decimal' THEN ( PG_ATTRIBUTE.ATTTYPMOD-4 ) / 65536 WHEN PG_TYPE.TYPNAME = 'date' THEN 10 WHEN PG_TYPE.TYPNAME = 'time' THEN 8 WHEN PG_TYPE.TYPNAME = 'timestamp' THEN 19 WHEN PG_TYPE.TYPNAME = 'bool' THEN 1 END AS FIELD_SIZE, CASE WHEN PG_ATTRIBUTE.ATTNOTNULL = TRUE THEN 0 WHEN PG_ATTRIBUTE.ATTNOTNULL = FALSE THEN 1 END AS IS_NULLABLE FROM PG_CLASS INNER JOIN PG_ATTRIBUTE ON PG_ATTRIBUTE.ATTRELID = PG_CLASS.OID INNER JOIN PG_TYPE ON PG_TYPE.OID = PG_ATTRIBUTE.ATTTYPID WHERE PG_CLASS.RELNAME = @TABLE_NAME AND PG_ATTRIBUTE.ATTNUM >= 0 AND PG_CLASS.RELKIND = 'r' ) AS A LEFT JOIN ( SELECT PG_CLASS.RELNAME AS TABLE_NAME, PG_ATTRIBUTE.ATTNUM AS FIELD_NUM, PG_ATTRIBUTE.ATTNAME AS FIELD_NAME, 1 AS IS_PK FROM PG_CLASS INNER JOIN PG_ATTRIBUTE ON PG_ATTRIBUTE.ATTRELID = PG_CLASS.OID INNER JOIN PG_CONSTRAINT ON PG_CLASS.OID = PG_CONSTRAINT.CONRELID WHERE PG_CLASS.RELNAME = @TABLE_NAME AND PG_CONSTRAINT.CONTYPE = 'p' AND PG_ATTRIBUTE.ATTNUM = ANY(PG_CONSTRAINT.CONKEY) ) AS B ON A.TABLE_NAME = B.TABLE_NAME AND A.FIELD_NUM = B.FIELD_NUM ORDER BY A.TABLE_NAME, A.FIELD_NUM
- Newer: [オープンソース]弁慶フレームワーク 1.1.0
- Older: 【PostgreSQL】テーブル一覧の取得
Comments:0
Trackbacks:0
- TrackBack URL for this entry
- http://magicbox.sakura.ne.jp/mt/mt-tb.cgi/496
- Listed below are links to weblogs that reference
- 【PostgreSQL】フィールド一覧の取得 from 爆裂!C#野郎