def return_table_schema_sql()

in dbconnectors/PgConnector.py [0:0]

• 34 lines of code
• 4 McCabe index (conditional complexity)

    def return_table_schema_sql(self, schema, table_names=None): 
        """
        This SQL returns a df containing the cols table_schema, table_name, table_description, table_columns (with cols in the table)
        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 
        - table_description: text descriptor, can be empty 
        - table_columns: aggregate of the col names inside the table 
        """

        table_filter_clause = ""

        if table_names:
            # Extract individual table names from the input string
            #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)})"""


        table_schema_sql = f'''
        SELECT table_schema, table_name,table_description, array_to_string(array_agg(column_name), ' , ') as table_columns
        FROM
        (select c.table_schema,c.table_name,c.column_name,c.ordinal_position,c.column_default,c.data_type,d.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} ) data
        GROUP BY table_schema, table_name, table_description
        ORDER BY table_name;
        '''

        return table_schema_sql