【PostgreSQL】フィールド一覧の取得

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