Function executeIntrospect

apps/sim/app/api/tools/postgresql/utils.ts:229–377

(
  sql: any,
  schemaName = 'public'
)

}

export async function executeIntrospect(
  sql: any,
  schemaName = 'public'
): Promise<IntrospectionResult> {
  const schemasResult = await sql`
    SELECT schema_name
    FROM information_schema.schemata
    WHERE schema_name NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
    ORDER BY schema_name
  `
  const schemas = schemasResult.map((row: { schema_name: string }) => row.schema_name)

  const tablesResult = await sql`
    SELECT table_name, table_schema
    FROM information_schema.tables
    WHERE table_schema = ${schemaName}
      AND table_type = 'BASE TABLE'
    ORDER BY table_name
  `

  const tables = []

  for (const tableRow of tablesResult) {
    const tableName = tableRow.table_name
    const tableSchema = tableRow.table_schema

    const columnsResult = await sql`
      SELECT
        c.column_name,
        c.data_type,
        c.is_nullable,
        c.column_default,
        c.udt_name
      FROM information_schema.columns c
      WHERE c.table_schema = ${tableSchema}
        AND c.table_name = ${tableName}
      ORDER BY c.ordinal_position
    `

    const pkResult = await sql`
      SELECT kcu.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
      WHERE tc.constraint_type = 'PRIMARY KEY'
        AND tc.table_schema = ${tableSchema}
        AND tc.table_name = ${tableName}
    `
    const primaryKeyColumns = pkResult.map((row: { column_name: string }) => row.column_name)

    const fkResult = await sql`
      SELECT
        kcu.column_name,
        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