An idea of a generic "scan whole table" function

[nicklasaven]

The inbuilt support of PostgreSQL json and jsonb in pgx gives some very nice possibilities.

I found out that it would be very easy to implement a function that can scan (more or less) any table into a struct without any manual mapping by just wrapping the query in the PostgreSQL native json functions.

Here is a complete example where I think the function here called runQuery could be a standard method There is no checking for connection in the function and so on. It is just to show how well pgx plays with PostgreSQL json functions.

import (
    "context"
    "fmt"
    "os"
    "strings"
    "github.com/jackc/pgx/v4"
)

func runQuery(conn *pgx.Conn, sql string, v interface{})(error){

    //Remove semicolon if present. Otherwise it will end up in the middle of the query
    //Tis also results in that multiple queries won't work of course
    sql = strings.Replace(sql, ";", "", -1)

    //Add PostgreSQL magic json functions
    //This gives us a single row back even if the query returns many rows
    //Now they get aggregated into a jsonb
    completeSql := fmt.Sprintf("SELECT jsonb_agg(row_to_json(orig_sql.*)) FROM (%s) orig_sql;", sql)

    //Just showing the actual sql we run
    fmt.Printf("completeSql = %s\n", completeSql)

    //get the result and unmarchal into our struct in one go
    //thanks to the great "out of the box" support of jsonb in pgx
    err := conn.QueryRow(context.Background(), completeSql).Scan(v)

    if err != nil {return err}

    return nil
}

func main(){

    conn, err := pgx.Connect(context.Background(), os.Getenv("DATABASE_URL"))
    if err != nil {
        fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
        os.Exit(1)
    }

}

// First example with single row

//Take care to use upper case first letter in field name even if db-fields have lower case or the scan will fail
    type id struct{
        Name string
        Number int      
    }

    var person []id

    err = runQuery(conn, "SELECT 123456 as number, 'Bill' as \"Name\"", &person)
    if err != nil {
        fmt.Fprintf(os.Stderr, "Query failed: %v\n", err)
        os.Exit(1)
    }

// Then example with multiple rows, all scaned together when coming from database
    type car struct{
        Brand string
        Color string
        Power int
        Fuel string
    }

    var cars []car

    sql := 
    `SELECT 'Volvo' as brand, 'red' as color, 100 as Power, 'petrol' as fuel
    union all
    SELECT 'BMW' as brand, 'white' as color, 200 as Power, 'petrol' as fuel
    union all
    SELECT 'Rimac ctwo' as brand, 'black' as color, 2000 as power, 'electricity' as fuel
    `

    err = runQuery(conn, sql, &cars)
    if err != nil {
        fmt.Fprintf(os.Stderr, "Query failed: %v\n", err)
        os.Exit(1)
    }

    conn.Close(context.Background())

// And print the results

    fmt.Printf("Person: %v\n", person)

    for _, c := range cars{
        fmt.Printf("Brand: %s, Color: %s, Power: %d, Fuel: %s\n", c.Brand, c.Color, c.Power, c.Fuel)
    }
}

This also works with output from database functions. But then it is important to name the field in the structure with the name of the function since that will be the field name the database sends

[nicklasaven]

By rewriting the runQuery function so it uses CTE it also works for queries like insert with returning clause:

func runQuery(conn *pgx.Conn, sql string, v interface{})(error){

//Remove semicolon if present. Otherwise it will end up in the middle of the query
//Tis also results in that multiple queries won't work of course
sql = strings.Replace(sql, ";", "", -1)

//Add PostgreSQL magic json functions
//This gives us a single row back even if the query returns many rows
//Now they get aggregated into a jsonb
completeSql := fmt.Sprintf(
`WITH orig_sql AS 
(%s) 
SELECT jsonb_agg(row_to_json(orig_sql.*)) 
FROM orig_sql;`,
sql)

//Just showing the actual sql we run
fmt.Printf("completeSql = %s\n", completeSql)

//get the result and unmarchal into our struct in one go
//thanks to the great "out of the box" support of jsonb in pgx
err := conn.QueryRow(context.Background(), completeSql).Scan(v)

if err != nil {return err}

return nil

}

[jackc]

I agree JSON probably is the simplest way to get complex structures out of PostgreSQL and mapped to Go and I use it extensively in my own applications.

But so far pgx has purposely avoided SQL / query building functionality and struct mapping. This might be an interesting direction to explore, but I think it would be best in a separate library.

[nicklasaven]

Yes, I agree that it is good to define what should be and what should not be in the library.

Thanks for a great library :-)