使用 pgx 和 go 在简单的 postgresql 查询中返回 id

Question

我正在尝试运行这个简单的查询来返回 id，根据作者的说法，我们使用 QueryRow 函数，好的，但是这个查询结果返回了一个期望 2 个 args 但在这里某处收到 3 个错误。

查询模型.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

    }

处理程序.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

我只是在这里使用一个结构来为我的处理程序注入依赖项。

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

}

Answer 1

从您收到的错误中：expected&nbsp;2&nbsp;arguments,&nbsp;got&nbsp;3我怀疑$3在 SQL 引号内的查询中不会被解释为参数。另外据我所知，这不是在 postgres 中使用参数间隔的正确方法。正确的方法是写interval '1 day' * $1所以我认为如果您将代码更改为：stmt&nbsp;:=&nbsp;`INSERT&nbsp;INTO&nbsp;widgets&nbsp;(title,&nbsp;content,&nbsp;created,&nbsp;expires)&nbsp;VALUES($1,&nbsp;$2,&nbsp;NOW(),&nbsp;NOW()&nbsp;+&nbsp;INTERVAL&nbsp;'1&nbsp;day'&nbsp;*&nbsp;$3)&nbsp;RETURNING&nbsp;id;`它会起作用的。但请确保将expires参数更改为 int 类型。

Answer 2

'$3 day' 您不能将查询参数作为字符串文字的一部分传递。尝试将所有INTERVAL '$3 day'部分替换为参数。像这个。func (m *WidgetModel) Insert(title, content, expires string) (int, error) {&nbsp; &nbsp; stmt := `INSERT INTO widgets (title, content, created, expires)VALUES ($1, $2, NOW(), NOW() + $3)RETURNING id;`&nbsp; &nbsp; var id int&nbsp; &nbsp; expiresDays, err := strconv.ParseInt(expires, 10, 32)&nbsp; &nbsp; if err != nil {&nbsp; &nbsp; &nbsp; &nbsp; return 0, err&nbsp; &nbsp; }&nbsp; &nbsp; expiresInterval := pgtype.Interval{&nbsp; &nbsp; &nbsp; &nbsp; Days:&nbsp; &nbsp;int32(expiresDays),&nbsp; &nbsp; &nbsp; &nbsp; Status: pgtype.Present}&nbsp; &nbsp; err = m.DB.QueryRow(context.Background(), stmt, title, content,&nbsp; &nbsp; &nbsp; &nbsp; expiresInterval).Scan(&id)&nbsp; &nbsp; if err != nil {&nbsp; &nbsp; &nbsp; &nbsp; if errors.Is(err, sql.ErrNoRows) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return 0, models.ErrNoRecord&nbsp; &nbsp; &nbsp; &nbsp; } else {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return 0, err&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; }&nbsp; &nbsp; return 0, nil}