pyqgis-list-column-description.py

import csv
import os
from qgis.PyQt.QtCore import QVariant

layer = iface.activeLayer()
uri_text = layer.dataProvider().uri().uri()
md = QgsProviderRegistry.instance().providerMetadata('postgres')
conn = md.createConnection(uri_text, {})

my_cols = [
    'table_catalog',
    'table_schema',
    'table_name',
    'column_name',
    'ordinal_position',
    'column_default',
    'is_nullable',
    'data_type',
    'character_maximum_length',
    'character_octet_length',
    'numeric_precision',
    'numeric_precision_radix',
    'numeric_scale',
    'datetime_precision',
    'interval_type',
    'interval_precision',
    'character_set_catalog',
    'character_set_schema',
    'character_set_name',
    'collation_catalog',
    'collation_schema',
    'collation_name',
    'domain_catalog',
    'domain_schema',
    'domain_name',
    'udt_catalog',
    'udt_schema',
    'udt_name',
    'scope_catalog',
    'scope_schema',
    'scope_name',
    'maximum_cardinality',
    'dtd_identifier',
    'is_self_referencing',
    'is_identity',
    'identity_generation',
    'identity_start',
    'identity_increment',
    'identity_maximum',
    'identity_minimum',
    'identity_cycle',
    'is_generated',
    'generation_expression',
    'is_updatable'
]

my_table_name = layer.dataProvider().uri().table()
my_query = f"""WITH columns_with_comment AS (
SELECT {','.join(my_cols)}, col_description((table_schema||'.'||table_name)::regclass::oid, ordinal_position) AS column_comment
  FROM information_schema.columns
 WHERE table_schema = 'public'
   AND table_name   = '{my_table_name}'
)
SELECT * FROM columns_with_comment
"""

result = conn.executeSql(my_query)

result = [['' if isinstance(el, QVariant) and el.isNull() else el for el in line] for line in result]


result = [my_cols + ['column_comment']] + result

# my_table_name
home_path = QgsProject.instance().homePath()
with open(os.path.join(home_path, f"{my_table_name}.csv"), "w") as f:
    writer = csv.writer(f)
    writer.writerows(result)

iface.addVectorLayer(os.path.join(home_path, f"{my_table_name}.csv", f"{my_table_name} metadata", "ogr")