getTableInfo method
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],
);
}