in dbconnectors/PgConnector.py [0:0]
def return_column_schema_sql(self, schema, table_names=None):
"""
This SQL returns a df containing the cols table_schema, table_name, column_name, data_type, column_description, table_description, primary_key, column_constraints
for the schema specified above, e.g. 'retail'
- table_schema: e.g. retail
- table_name: name of the table inside the schema, e.g. products
- column_name: name of each col in each table in the schema, e.g. id_product
- data_type: data type of each col
- column_description: col descriptor, can be empty
- table_description: text descriptor, can be empty
- primary_key: whether the col is PK; if yes, the field contains the col_name
- column_constraints: e.g. "Primary key for this table"
"""
table_filter_clause = ""
if table_names:
# table_names = [name.strip() for name in table_names[1:-1].split(",")] # Handle the string as a list
formatted_table_names = [f"'{name}'" for name in table_names]
table_filter_clause = f"""and table_name in ({', '.join(formatted_table_names)})"""
column_schema_sql = f'''
WITH
columns_schema
AS
(select c.table_schema,c.table_name,c.column_name,c.data_type,d.description as column_description, obj_description(c1.oid) as table_description
from information_schema.columns c
inner join pg_class c1
on c.table_name=c1.relname
inner join pg_catalog.pg_namespace n
on c.table_schema=n.nspname
and c1.relnamespace=n.oid
left join pg_catalog.pg_description d
on d.objsubid=c.ordinal_position
and d.objoid=c1.oid
where
c.table_schema='{schema}' {table_filter_clause}) ,
pk_schema as
(SELECT table_name, column_name AS primary_key
FROM information_schema.key_column_usage