Returning id in simple postgresql query using pgx and go-CodePudding

I'm trying to run this simple query to return the id, according to the author we use the QueryRow function, ok, but this query result returns a expects 2 args but received 3 error somewhere in here.

querymodel.go

 type WidgetetModel struct {
    DB *pgxpool.Pool
}

func (m *WidgetModel) Insert(title, content, expires string) (int, error) {
        stmt := `INSERT INTO widgets (title, content, created, expires) VALUES($1, $2, NOW(), NOW()   INTERVAL '$3 day') RETURNING id;`
    
        var id int
        err := m.DB.QueryRow(context.Background(), stmt, title, content, expires).Scan(&id)
        if err != nil {
            if errors.Is(err, sql.ErrNoRows) {
                return 0, models.ErrNoRecord
            } else {
                return 0, err
            }
        }
    
        return 0, nil
    }

handlers.go

func (app *application) createWidget(w http.ResponseWriter, r *http.Request) {
    if r.Method != http.MethodPost {
        w.Header().Set("Allow", http.MethodPost)
        app.clientError(w, http.StatusMethodNotAllowed) // Use the clientError() helper.
        return
    }

    title := "Widget 1"
    content := "Some content here..."
    expires := "7"

    id, err := app.widgets.Insert(title, content, expires)
    if err != nil {
        app.serverError(w, err) <-- line 57
        return
    }

    http.Redirect(w, r, fmt.Sprintf("/widget?id=%v", id), http.StatusSeeOther)
}

main.go

I'm just using a struct to inject dependencies for my handlers here.

dbPool, err := openDB(*dsn)
    if err != nil {
        errorLog.Fatal(err)
    }
    defer dbPool.Close()

app := &application{
        errorLog: errorLog,
        infoLog:  infoLog,
        snippets: &postgresql.WidgetModel{DB: dbPool},
}

///

func openDB(dsn string) (*pgxpool.Pool, error) {
    pool, err := pgxpool.Connect(context.Background(), dsn)
    if err != nil {
        return nil, err
    }
    if err = pool.Ping(context.Background()); err != nil {
        return nil, err
    }
    return pool, nil
}

error output

go run ./cmd/web                                      
INFO    2022/02/20 17:19:30 Starting server on :4000
ERROR   2022/02/20 17:19:38 handlers.go:57: expected 2 arguments, got 3
goroutine 34 [running]:
runtime/debug.Stack()
        /usr/local/go/src/runtime/debug/stack.go:24  0x88
main.(*application).serverError(0x1400008e000, {0x1005c7be8, 0x1400009a1c0}, {0x1005bf2c0, 0x14000094120})
        /Users/spencerlong/Desktop/Golang/Snippetts/cmd/web/helpers.go:12  0x44
main.(*application).createSnippet(0x1400008e000, {0x1005c7be8, 0x1400009a1c0}, 0x140000c8000)
        /Users/spencerlong/Desktop/Golang/Snippetts/cmd/web/handlers.go:57  0x200
net/http.HandlerFunc.ServeHTTP(0x14000094020, {0x1005c7be8, 0x1400009a1c0}, 0x140000c8000)
        /usr/local/go/src/net/http/server.go:2046  0x40
net/http.(*ServeMux).ServeHTTP(0x14000092000, {0x1005c7be8, 0x1400009a1c0}, 0x140000c8000)
        /usr/local/go/src/net/http/server.go:2424  0x18c
net/http.serverHandler.ServeHTTP({0x1400009a000}, {0x1005c7be8, 0x1400009a1c0}, 0x140000c8000)
        /usr/local/go/src/net/http/server.go:2878  0x444
net/http.(*conn).serve(0x140000b0000, {0x1005ca280, 0x14000096180})
        /usr/local/go/src/net/http/server.go:1929  0xb6c
created by net/http.(*Server).Serve
        /usr/local/go/src/net/http/server.go:3033  0x4b8

helpers.go

func (app *application) serverError(w http.ResponseWriter, err error) {
    trace := fmt.Sprintf("%s\n%s", err.Error(), debug.Stack())
    app.errorLog.Output(2, trace)

    http.Error(w, http.StatusText(http.StatusInternalServerError), http.StatusInternalServerError)
}

CodePudding user response：

From the error you are receiving:

expected 2 arguments, got 3

I suspect that $3 in your query which is inside a SQL quote is not interpreted as a parameter. Also to my knowledge, this is not a correct way of using parametric intervals in postgres. The correct way would be to write interval '1 day' * $1

So I think if you change your code to:

stmt := `INSERT INTO widgets (title, content, created, expires) VALUES($1, $2, NOW(), NOW()   INTERVAL '1 day' * $3) RETURNING id;`

It will work. But make sure to change your expires parameter to type int.

CodePudding user response：

'$3 day' You cannot pass query parameter as part of string literal.

Try to replace all INTERVAL '$3 day' part as a parameter. Like this one.

func (m *WidgetModel) Insert(title, content, expires string) (int, error) {
    stmt := `
INSERT INTO widgets (title, content, created, expires)
VALUES ($1, $2, NOW(), NOW()   $3)
RETURNING id;`

    var id int
    expiresDays, err := strconv.ParseInt(expires, 10, 32)
    if err != nil {
        return 0, err
    }
    expiresInterval := pgtype.Interval{
        Days:   int32(expiresDays),
        Status: pgtype.Present}
    err = m.DB.QueryRow(context.Background(), stmt, title, content,
        expiresInterval).Scan(&id)
    if err != nil {
        if errors.Is(err, sql.ErrNoRows) {
            return 0, models.ErrNoRecord
        } else {
            return 0, err
        }
    }

    return 0, nil
}