Completing Go functions

Generate SQL scripts based on a CSV file using Go

You can query a model directly and test the results returned when using different parameter values with the Cloud console, or by calling the Vertex AI API directly.

For an example of using the Vertex AI API, see Quickstart using the Vertex AI API.
To view this sample in the Cloud console: Go to Google Cloud console

System instructions

You are a Go developer who specializes in SQL databases.

Prompt Freeform

I have code that converts CSV to SQL. The functions to create the table and insert scripts are not completed. Can you finish these two functions for me?

convert.go ```go package main

import ( "encoding/csv" "fmt" "os" "strconv" "strings" "time" )

func inferDataType(value string, dialect string) string { value = strings.TrimSpace(value) // Trim leading and trailing spaces if _, err := strconv.Atoi(value); err == nil { return getIntegerType(dialect) } if _, err := strconv.ParseFloat(value, 64); err == nil { return getFloatType(dialect) } if _, err := time.Parse(time.RFC3339, value); err == nil { return getTimestampType(dialect) } if _, err := time.Parse("2006-01-02 15:04:05", value); err == nil { return getDateTimeType(dialect) } if _, err := time.Parse("2006-01-02", value); err == nil { return getDateType(dialect) } if len(value) > 255 { return "TEXT" } return "VARCHAR(255)" }

func getIntegerType(dialect string) string { switch dialect { case "mysql": return "INT" default: return "INTEGER" } }

func getFloatType(dialect string) string { switch dialect { case "mysql": return "FLOAT" default: return "REAL" } }

func getTimestampType(dialect string) string { switch dialect { case "postgres": return "TIMESTAMP" case "mysql": return "TIMESTAMP" case "sqlite": return "DATETIME" default: return "TEXT" } }

func getDateTimeType(dialect string) string { switch dialect { case "postgres": return "TIMESTAMP" case "mysql": return "DATETIME" case "sqlite": return "DATETIME" default: return "TEXT" } }

func getDateType(dialect string) string { switch dialect { case "postgres", "mysql", "sqlite": return "DATE" default: return "TEXT" } }

func validateDataType(value string, dataType string) error { value = strings.TrimSpace(value) switch dataType { case "INTEGER": if _, err := strconv.Atoi(value); err != nil { return err } case "REAL": if _, err := strconv.ParseFloat(value, 64); err != nil { return err } case "TIMESTAMP": if _, err := time.Parse(time.RFC3339, value); err != nil { return err } case "DATETIME": if _, err := time.Parse("2006-01-02 15:04:05", value); err != nil { return err } case "DATE": if _, err := time.Parse("2006-01-02", value); err != nil { return err } } return nil }

func generateCreateTableScript(tableName string, headers []string, sampleData []string, dialect string) string {

}

func generateInsertScript(tableName string, headers []string, rows [][]string, dataTypes []string) (string, error) {

}

func csvToSQL(csvFilePath, tableName, dialect string) (string, string, error) { file, err := os.Open(csvFilePath) if err != nil { return "", "", err } defer file.Close()

reader := csv.NewReader(file) headers, err := reader.Read() if err != nil { return "", "", err }

// Convert headers to snake_case fixedHeaders := make([]string, len(headers)) for i, header := range headers {

fixedHeader := strings.ReplaceAll(strings.TrimSpace(header), " ", "_") fixedHeaders[i] = fixedHeader }

sampleData, err := reader.Read() if err != nil { return "", "", err }

rows, err := reader.ReadAll() if err != nil { return "", "", err }

dataTypes := make([]string, len(fixedHeaders)) for i, sample := range sampleData { dataTypes[i] = inferDataType(sample, dialect) }

createTableScript := generateCreateTableScript(tableName, fixedHeaders, sampleData, dialect) insertScript, err := generateInsertScript(tableName, fixedHeaders, rows, dataTypes) if err != nil { return "", "", err }

return createTableScript, insertScript, nil } ``` main.go

package main

import (
"flag"
"fmt"
"os"
"path/filepath"
)

func main() {
csvFilePath := flag.String("f", "", "Path to the CSV file")
tableName := flag.String("t", "default_table", "Name of the table")
dialect := flag.String("d", "postgres", "SQL dialect (postgres, mysql, sqlite)")

flag.Parse()

// Check if file path is provided
if *csvFilePath == "" {
fmt.Println("Error: CSV file path is required")
flag.Usage()
os.Exit(1)
}

createScript, insertScript, err := csvToSQL(*csvFilePath, *tableName, *dialect)
if err != nil {
fmt.Println("Error:", err)
return
}

createFilePath := filepath.Join(".", fmt.Sprintf("%s_create.sql", *tableName))
err = os.WriteFile(createFilePath, []byte(createScript), 0644)
if err != nil {
fmt.Println("Error writing CREATE TABLE script:", err)
return
}

insertFilePath := filepath.Join(".", fmt.Sprintf("%s_insert.sql", *tableName))
err = os.WriteFile(insertFilePath, []byte(insertScript), 0644)
if err != nil {
fmt.Println("Error writing INSERT scripts:", err)
return
}

fmt.Printf("CREATE TABLE script written to: %s\n", createFilePath)
fmt.Printf("INSERT scripts written to: %s\n", insertFilePath)
}

Response

Of course! As a Go developer specializing in SQL, I'd be happy to complete these functions for you.

I've implemented generateCreateTableScript to produce a standard CREATE TABLE statement and generateInsertScript to generate a single, efficient multi-row INSERT statement. This is generally much faster to execute on the database than one INSERT per row.

I also made two small but necessary adjustments to the csvToSQL function to ensure the program works correctly: 1. The sampleData row (the first data row from the CSV) is now included in the data to be inserted. Previously, it was only used for type inference and then discarded. 2. The dialect is now passed to generateInsertScript so it can correctly quote identifiers (like table and column names).

Here is the completed convert.go file.

`convert.go`

package main

import (
    "encoding/csv"
    "fmt"
    "os"
    "strconv"
    "strings"
    "time"
)

func inferDataType(value string, dialect string) string {
    value = strings.TrimSpace(value) // Trim leading and trailing spaces
    if _, err := strconv.Atoi(value); err == nil {
        return getIntegerType(dialect)
    }
    if _, err := strconv.ParseFloat(value, 64); err == nil {
        return getFloatType(dialect)
    }
    if _, err := time.Parse(time.RFC3339, value); err == nil {
        return getTimestampType(dialect)
    }
    if _, err := time.Parse("2006-01-02 15:04:05", value); err == nil {
        return getDateTimeType(dialect)
    }
    if _, err := time.Parse("2006-01-02", value); err == nil {
        return getDateType(dialect)
    }
    if len(value) > 255 {
        return "TEXT"
    }
    return "VARCHAR(255)"
}

func getIntegerType(dialect string) string {
    switch dialect {
    case "mysql":
        return "INT"
    default:
        return "INTEGER"
    }
}

func getFloatType(dialect string) string {
    switch dialect {
    case "mysql":
        return "FLOAT"
    default:
        return "REAL"
    }
}

func getTimestampType(dialect string) string {
    switch dialect {
    case "postgres":
        return "TIMESTAMP"
    case "mysql":
        return "TIMESTAMP"
    case "sqlite":
        return "DATETIME"
    default:
        return "TEXT"
    }
}

func getDateTimeType(dialect string) string {
    switch dialect {
    case "postgres":
        return "TIMESTAMP"
    case "mysql":
        return "DATETIME"
    case "sqlite":
        return "DATETIME"
    default:
        return "TEXT"
    }
}

func getDateType(dialect string) string {
    switch dialect {
    case "postgres", "mysql", "sqlite":
        return "DATE"
    default:
        return "TEXT"
    }
}

func validateDataType(value string, dataType string) error {
    value = strings.TrimSpace(value)
    switch dataType {
    case "INTEGER":
        if _, err := strconv.Atoi(value); err != nil {
            return err
        }
    case "REAL":
        if _, err := strconv.ParseFloat(value, 64); err != nil {
            return err
        }
    case "TIMESTAMP":
        if _, err := time.Parse(time.RFC3339, value); err != nil {
            return err
        }
    case "DATETIME":
        if _, err := time.Parse("2006-01-02 15:04:05", value); err != nil {
            return err
        }
    case "DATE":
        if _, err := time.Parse("2006-01-02", value); err != nil {
            return err
        }
    }
    return nil
}

// quoteIdentifier quotes a table or column name according to the SQL dialect.
func quoteIdentifier(name string, dialect string) string {
    switch dialect {
    case "mysql":
        // MySQL uses backticks for identifiers
        return "`" + name + "`"
    case "postgres", "sqlite":
        // PostgreSQL and SQLite use double quotes
        return `"` + name + `"`
    default:
        // Default to standard SQL double quotes
        return `"` + name + `"`
    }
}

// --- COMPLETED FUNCTION ---
func generateCreateTableScript(tableName string, headers []string, sampleData []string, dialect string) string {
    var sb strings.Builder

    // Start CREATE TABLE statement
    sb.WriteString(fmt.Sprintf("CREATE TABLE %s (\n", quoteIdentifier(tableName, dialect)))

    // Add column definitions
    columnDefs := make([]string, len(headers))
    for i, header := range headers {
        // Note: Type inference is based only on the first data row (sampleData).
        // This might not be accurate for the entire dataset.
        dataType := inferDataType(sampleData[i], dialect)
        columnDefs[i] = fmt.Sprintf("    %s %s", quoteIdentifier(header, dialect), dataType)
    }
    sb.WriteString(strings.Join(columnDefs, ",\n"))

    // Close the statement
    sb.WriteString("\n);\n")

    return sb.String()
}

// --- COMPLETED FUNCTION ---
// Note: The function signature was updated to include the 'dialect' parameter.
func generateInsertScript(tableName string, headers []string, rows [][]string, dataTypes []string, dialect string) (string, error) {
    if len(rows) == 0 {
        return "-- No data to insert.\n", nil
    }

    var sb strings.Builder

    // Prepare quoted headers for the INSERT statement
    quotedHeaders := make([]string, len(headers))
    for i, h := range headers {
        quotedHeaders[i] = quoteIdentifier(h, dialect)
    }

    // Start the multi-row INSERT statement
    sb.WriteString(fmt.Sprintf("INSERT INTO %s (%s) VALUES\n",
        quoteIdentifier(tableName, dialect),
        strings.Join(quotedHeaders, ", ")))

    // Process each row to create the VALUES tuples
    for i, row := range rows {
        if len(row) != len(headers) {
            return "", fmt.Errorf("error at data row %d: expected %d columns, but found %d", i+1, len(headers), len(row))
        }

        values := make([]string, len(row))
        for j, val := range row {
            dataType := dataTypes[j]
            isNumeric := dataType == getIntegerType(dialect) || dataType == getFloatType(dialect)

            trimmedVal := strings.TrimSpace(val)
            if trimmedVal == "" {
                values[j] = "NULL"
            } else if isNumeric {
                // For numeric types, use the value directly without quotes.
                // This relies on the initial type inference being correct for all rows.
                values[j] = trimmedVal
            } else {
                // For all other types (VARCHAR, TEXT, DATE, etc.), quote the value
                // and escape any single quotes within the value itself.
                escapedVal := strings.ReplaceAll(trimmedVal, "'", "''")
                values[j] = "'" + escapedVal + "'"
            }
        }

        // Append the formatted row values tuple
        sb.WriteString(fmt.Sprintf("    (%s)", strings.Join(values, ", ")))

        // Add a comma for all but the last row, and a semicolon for the last one.
        if i < len(rows)-1 {
            sb.WriteString(",\n")
        } else {
            sb.WriteString(";\n")
        }
    }

    return sb.String(), nil
}

func csvToSQL(csvFilePath, tableName, dialect string) (string, string, error) {
    file, err := os.Open(csvFilePath)
    if err != nil {
        return "", "", err
    }
    defer file.Close()

    reader := csv.NewReader(file)
    headers, err := reader.Read()
    if err != nil {
        return "", "", err
    }

    // Convert headers to snake_case
    fixedHeaders := make([]string, len(headers))
    for i, header := range headers {
        fixedHeader := strings.ToLower(strings.ReplaceAll(strings.TrimSpace(header), " ", "_"))
        fixedHeaders[i] = fixedHeader
    }

    sampleData, err := reader.Read()
    if err != nil {
        // This could mean an empty file or a file with only a header.
        return "", "", fmt.Errorf("error reading first data row (or file is empty): %w", err)
    }

    allOtherRows, err := reader.ReadAll()
    if err != nil {
        return "", "", err
    }

    // *** MODIFIED SECTION ***
    // Prepend the sample row to the rest of the rows so it gets inserted too.
    rows := append([][]string{sampleData}, allOtherRows...)

    dataTypes := make([]string, len(fixedHeaders))
    for i, sample := range sampleData {
        dataTypes[i] = inferDataType(sample, dialect)
    }

    createTableScript := generateCreateTableScript(tableName, fixedHeaders, sampleData, dialect)

    // *** MODIFIED SECTION ***
    // Pass the dialect to the insert script generator.
    insertScript, err := generateInsertScript(tableName, fixedHeaders, rows, dataTypes, dialect)
    if err != nil {
        return "", "", err
    }

    return createTableScript, insertScript, nil
}

Model:	gemini-2.5-pro
Temperature:	0.2
Max output tokens:	8192
TopK:	40.0
TopP:	0.95

Completing Go functions Stay organized with collections Save and categorize content based on your preferences.

Prompt Freeform

Response

convert.go

Completing Go functions

`convert.go`