PostgreSQL Golang Martini插入密钥

Question

I am working on building a social network type server as an exercise using martini, golang, and postgresql to help develop my skills in all three. The few key things that are eluding me are how to insert the primary key from the user table into the correct row of the user info table (to connect the user info with the specific user). I'm sure there is also a way to consolidate both the queries into a much more concise postgres script...

func CreateUser(ren render.Render, r *http.Request, db *sql.DB) {

  _, err := db.Query("INSERT INTO users (first, last, email, password, karma, value) SELECT  CAST ($3 AS VARCHAR), $1, $2, $4, $5, $6 WHERE NOT EXISTS (SELECT * FROM users WHERE email = $3)",
    r.FormValue("first"),
    r.FormValue("last"),
    r.FormValue("email"),
    r.FormValue("password"),
    0,
    0)

  PanicIf(err)

  _, err = db.Query("INSERT INTO userinfo (usr, dob, phonenum, bio, mates, bought, sold) VALUES ($1, $2, $3, $4, $5, $6, $7)",
        0,
        r.FormValue("dob"),
        r.FormValue("phonenum"),
        r.FormValue("bio"),
        0,
        0,
        0)

  PanicIf(err)

  ren.Redirect("/")
}

Here is the script for creating the user table:

DROP TABLE IF EXISTS "public"."users";
CREATE TABLE "public"."users" (
    "id" serial NOT NULL,
    "first" varchar(40) NOT NULL COLLATE "default",
    "last" varchar(40) NOT NULL COLLATE "default",
    "email" varchar(40) NOT NULL COLLATE "default",
    "password" varchar(40) NOT NULL COLLATE "default",
    "karma" int NOT NULL,
    "value" int NOT NULL
)
WITH (OIDS=FALSE);

I realize that in production level code I would not want to store the password like this. I have also sort of been stumped by how to pull non-global parameters into the database as its is created by m.get & m.post commands.

 m.Post("/newuser", CreateUser)
 m.Get("/user", NewUser)

Any advice on consolidating and adding the key from users to the appropriate entry in userinfo would be greatly appreciated... I realize that these are probably incredibly stupid questions but please pardon my naivety.

Answer 1

I did it by returning the inserted user id from the first query and use it in the second one, but I did not use upsert.

So you can try use RETURNING to return the id of newly added user or query it separately by the email (as it looks like the email is unique per user in this case).

Then use it for second query:

_, err = db.Query(`INSERT INTO userinfo (usr, dob, phonenum, bio, mates, bought, sold)
                   VALUES ($1, $2, $3, $4, $5, $6, $7)`,
    id, // there
    r.FormValue("dob"),
    r.FormValue("phonenum"),
    ...

I suppose that usr is the user id (foreign key to users.id)

Sample source code:

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/lib/pq"
    "log"
)

func main() {
    db, err := sql.Open("postgres", "user=alex dbname=tmp sslmode=disable")
    if err != nil {
        log.Fatal(err)
    }

    rows, err := db.Query(`
        INSERT INTO users (first, last, email, password, karma, value)
        SELECT  CAST ($3 AS VARCHAR), $1, $2, $4, $5, $6
        WHERE NOT EXISTS (SELECT * FROM users WHERE email = $3)
        RETURNING id`,
        "first", "last", "email", "password", 0, 0)
    defer rows.Close()
    if err != nil {
        log.Fatal(err)
    }

    if rows.Next() {
        id := 0
        rows.Scan(&id)
        _, err = db.Exec(`INSERT INTO userinfo (user_id, info) VALUES ($1, $2)`, id, "info")
        if err != nil {
            log.Fatal(err)
        }

        fmt.Println("Inserted:", id)
    }

}

I personally prefer QueryRow instead of query as it a bit more obvious that one row is returned, but it will work with Query as well.

Also I recommend to use unique index for email, instead of subquery.