PostgreSQL - 获取物化视图列元数据

PostgreSQL - 获取物化视图列元数据

sql
SELECT a.attname AS column_name,
       format_type(a.atttypid, a.atttypmod) AS data_type,
       a.attnotnull AS not_null,
       pgd.description AS column_description
FROM pg_class AS c
JOIN pg_attribute AS a ON c.oid = a.attrelid
LEFT JOIN pg_description AS pgd ON pgd.objoid = c.oid AND pgd.objsubid = a.attnum
WHERE c.relname = 'my_materialized_view';

python
import psycopg2
def get_materialized_view_columns(connection, view_name):
    cursor = connection.cursor()
    query = """
    SELECT a.attname AS column_name,
           format_type(a.atttypid, a.atttypmod) AS data_type,
           a.attnotnull AS not_null,
           pgd.description AS column_description
    FROM pg_class AS c
    JOIN pg_attribute AS a ON c.oid = a.attrelid
    LEFT JOIN pg_description AS pgd ON pgd.objoid = c.oid AND pgd.objsubid = a.attnum
    WHERE c.relname = %s;
    """
    cursor.execute(query, (view_name,))
    columns = cursor.fetchall()
    return columns
# 连接到 PostgreSQL 数据库
connection = psycopg2.connect(
    host="localhost",
    port="5432",
    database="my_database",
    user="my_user",
    password="my_password"
)
# 获取物化视图的列元数据
materialized_view_name = "my_materialized_view"
columns = get_materialized_view_columns(connection, materialized_view_name)
# 打印列元数据
for column in columns:
    print("Column Name:", column[0])
    print("Data Type:", column[1])
    print("Not Null:", column[2])
    print("Column Description:", column[3])
    print()
# 关闭数据库连接
connection.close()