dbd-altpg-columns.sql

-- Let PG compute as many of the DBI attributes as is practicable --
SELECT
  a.attname                    AS name,
  t.typname                    AS type_name,
  CASE
    WHEN a.attlen > 0 THEN a.attlen
    WHEN a.atttypmod > 65535 THEN a.atttypmod >> 16
    WHEN a.atttypmod >= 4 THEN a.atttypmod - 4
    ELSE NULL
  END                          AS precision,
  CASE
    WHEN a.attlen <= 0 AND a.atttypmod > 65535 THEN (a.atttypmod & 65535) - 4
    ELSE NULL
  END                          AS scale,
  NOT a.attnotnull             AS nullable,
  d.adsrc                      AS default,
  COALESCE(ii.indexed, false)  AS indexed,
  COALESCE(iu.unique, false)   AS unique,
  COALESCE(ip.primary, false)  AS primary,
  a.atttypid                   AS pg_type,
  a.attlen                     AS pg_typlen
FROM
  pg_catalog.pg_class c
  INNER JOIN
  pg_catalog.pg_attribute a ON c.oid = a.attrelid
  INNER JOIN
  pg_catalog.pg_type t ON t.oid = a.atttypid
  LEFT JOIN
  pg_catalog.pg_attrdef d ON d.adrelid = c.oid AND d.adnum = a.attnum
  LEFT JOIN
  (SELECT                          -- ii: is column indexed at all?
     ipa.attname AS attname,
     i0.indrelid AS tbl_oid,
     true        AS indexed
   FROM
     pg_catalog.pg_attribute ipa
   INNER JOIN
     pg_catalog.pg_index i0 ON i0.indexrelid = ipa.attrelid
   GROUP BY 1, 2) ii
     ON ii.attname = a.attname AND ii.tbl_oid = c.oid
  LEFT JOIN
  (SELECT                          -- iu: part of a UNIQUE index?
     iua.attname AS attname,
     i1.indrelid AS tbl_oid,
     true        AS unique
   FROM
     pg_catalog.pg_attribute iua
   INNER JOIN
     pg_catalog.pg_index i1 ON i1.indexrelid = iua.attrelid
   WHERE
     i1.indisunique
   GROUP BY 1, 2, 3) iu
     ON iu.attname = a.attname AND iu.tbl_oid = c.oid
  LEFT JOIN
  (SELECT                          -- ip: part of a PRIMARY key?
     ipa.attname AS attname,
     i2.indrelid AS tbl_oid,
     true        AS primary
   FROM
     pg_catalog.pg_attribute ipa
   INNER JOIN
     pg_catalog.pg_index i2 ON i2.indexrelid = ipa.attrelid
   WHERE
     i2.indisprimary
   GROUP BY 1, 2, 3) ip
     ON ip.attname = a.attname AND ip.tbl_oid = c.oid
WHERE
  a.attnum > 0                          -- regular column
    AND
  NOT a.attisdropped                    -- which has not been dropped
    AND
  c.relkind IN ('r','v')                -- belonging to a TABLE or VIEW
    AND
  c.relname = ?                         -- named ?, which TABLE/VIEW is
    AND
  pg_catalog.pg_table_is_visible(c.oid) -- visible without qualification
ORDER BY
  a.attnum ASC