getTableInfo method

  1. @override
Statement getTableInfo(
  1. QualifiedTablename table
)
override

Fetches information about the columns of a table. The information includes all column names, their type, whether or not they are nullable, and whether they are part of the PK.

Implementation

@override
Statement getTableInfo(QualifiedTablename table) {
  return Statement(
    r'''
      SELECT
        c.column_name AS name,
        UPPER(c.data_type) AS type,
        CASE
          WHEN c.is_nullable = 'YES' THEN 0
          ELSE 1
        END AS notnull,
        c.column_default AS dflt_value,
        COALESCE(
          (
            -- Subquery to determine if the column is part of the primary key and
            -- its position. We +1 to the position as we return 0 if the column
            -- is not part of the primary key.
            SELECT array_position(ind.indkey, att.attnum) + 1
            FROM pg_class cl
            JOIN pg_attribute att ON cl.oid = att.attrelid
            JOIN pg_index ind ON cl.oid = ind.indrelid
            JOIN pg_constraint con ON con.conindid = ind.indexrelid
            WHERE cl.relname = c.table_name  -- Match the table name
              AND att.attname = c.column_name  -- Match the column name
              AND cl.relnamespace = (
                SELECT oid FROM pg_namespace WHERE nspname = c.table_schema
              )  -- Match the schema
              AND con.contype = 'p'  -- Only consider primary key constraints
          ),
          0  -- If the column is not part of the primary key, return 0
      ) AS pk
      FROM information_schema.columns AS c
      WHERE
        c.table_name = $1 AND
        c.table_schema = $2;
    ''',
    [table.tablename, table.namespace],
  );
}