llm-ready-postgresql-schema-details.md

LLM Ready PostgreSQL SQL command

script.sql creates a JSON

Process this JSOn to be LLM Ready

{...}

From this PostgreSQL schema overview, I want you to give a nicely formatted context prompt for my agent named "SQL Expert", be precise and help my agents with this raw data

script.sql

WITH 
table_info AS (
    SELECT
        t.table_schema,
        t.table_name,
        obj_description(pgc.oid) AS table_description,
        pg_size_pretty(pg_total_relation_size(pgc.oid)) AS table_size,
        pg_stat_get_live_tuples(pgc.oid) AS row_count
    FROM
        information_schema.tables t
    JOIN
        pg_class pgc ON pgc.relname = t.table_name
    JOIN
        pg_namespace nsp ON nsp.oid = pgc.relnamespace AND nsp.nspname = t.table_schema
    WHERE
        t.table_schema = 'public'
        AND t.table_type = 'BASE TABLE'
),
column_info AS (
    SELECT
        c.table_schema,
        c.table_name,
        c.column_name,
        c.data_type,
        c.column_default,
        c.is_nullable,
        c.character_maximum_length,
        c.numeric_precision,
        c.numeric_scale,
        col_description(pgc.oid, c.ordinal_position) AS column_description
    FROM
        information_schema.columns c
    JOIN
        pg_class pgc ON pgc.relname = c.table_name
    JOIN
        pg_namespace nsp ON nsp.oid = pgc.relnamespace AND nsp.nspname = c.table_schema
    WHERE
        c.table_schema = 'public'
),
constraint_info AS (
    SELECT
        tc.table_schema,
        tc.table_name,
        tc.constraint_name,
        tc.constraint_type,
        kcu.column_name,
        ccu.table_schema AS foreign_table_schema,
        ccu.table_name AS foreign_table_name,
        ccu.column_name AS foreign_column_name
    FROM
        information_schema.table_constraints tc
    JOIN
        information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name
        AND tc.table_schema = kcu.table_schema
    LEFT JOIN
        information_schema.constraint_column_usage ccu ON ccu.constraint_name = tc.constraint_name
        AND ccu.table_schema = tc.table_schema
    WHERE
        tc.table_schema = 'public'
),
index_info AS (
    SELECT
        schemaname AS table_schema,
        tablename AS table_name,
        indexname AS index_name,
        indexdef AS index_definition
    FROM
        pg_indexes
    WHERE
        schemaname = 'public'
)

SELECT json_build_object(
    'database_info', (
        SELECT json_build_object(
            'database_name', current_database(),
            'database_size', pg_size_pretty(pg_database_size(current_database())),
            'version', version()
        )
    ),
    'tables', (
        SELECT json_agg(json_build_object(
            'schema', table_schema,
            'name', table_name,
            'description', table_description,
            'size', table_size,
            'row_count', row_count,
            'columns', (
                SELECT json_agg(json_build_object(
                    'name', column_name,
                    'data_type', data_type,
                    'default', column_default,
                    'is_nullable', is_nullable,
                    'max_length', character_maximum_length,
                    'numeric_precision', numeric_precision,
                    'numeric_scale', numeric_scale,
                    'description', column_description
                ))
                FROM column_info ci
                WHERE ci.table_schema = ti.table_schema AND ci.table_name = ti.table_name
            ),
            'constraints', (
                SELECT json_agg(json_build_object(
                    'name', constraint_name,
                    'type', constraint_type,
                    'column', column_name,
                    'foreign_table_schema', foreign_table_schema,
                    'foreign_table', foreign_table_name,
                    'foreign_column', foreign_column_name
                ))
                FROM constraint_info coi
                WHERE coi.table_schema = ti.table_schema AND coi.table_name = ti.table_name
            ),
            'indexes', (
                SELECT json_agg(json_build_object(
                    'name', index_name,
                    'definition', index_definition
                ))
                FROM index_info ii
                WHERE ii.table_schema = ti.table_schema AND ii.table_name = ti.table_name
            )
        ))
        FROM table_info ti
    )
) AS database_metadata;