Returning id in simple postgresql query using pgx and go

Issue

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)
}

Solution

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.

Answered By – Maziar Taheri

Answer Checked By – Cary Denson (GoLangFix Admin)

Leave a Reply

Your email address will not be published.