I wonder if it's possible to pass parameters to a query written in PL/pgSQL?
I tried this, but it failed with pq: got 1 parameters but the statement requires 0
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/lib/pq"
)
func main() {
db, err := sql.Open("postgres", "host=localhost dbname=db user=user sslmode=disable password=pw")
if err != nil {
log.Fatal(err)
}
row := db.QueryRow(`
DO $$
BEGIN
IF true THEN
SELECT $1;
END IF;
END$$
`, 1)
var num int
err = row.Scan(&num)
if err != nil {
log.Fatal(err)
}
fmt.Println(num)
}
Another related question is that I want to use transactions, but the APIs provided by the sql
package seems to connect to db every time a query is executed in a tx. I'd like everything to be executed in one go if that's possible. For example, with go you are supposed to use transactions like this
tx, err := db.Begin()
rows, err := tx.Query(sql1)
result, err := tx.Exec(sql2)
tx.Commit()
The problem is that calling tx.Query
and tx.Exec
makes two trips to PostgreSQL server if I'm not wrong. What I want to achieve is to merge sql1
and sql2
, wrap them inside BEGIN
and END
and execute them in one trip. And my question is that:
- Do you think it's necessary? I imagine that with enough traffic, the performance difference might be obvious, but I'm not sure.
- If so, what's the best way to execute this merged transaction? Create a function and run the transaction inside PL/pgSQL (since I might need to use conditional statements, etc)?