I use a temporary table to hold a range of ID's so I can use them in several other queries without adding a long list of ID's to every query.
I'm building this in GO and this is new for me. Creating the temporary table works, fetching the ID's succeed and also adding those IDs to the temporary table is successful. But when I use the temporary table I get this error:
pq: relation "temp_id_table" does not exist
This is my code (EDITED: added transaction):
//create context
ctx, cancel := context.WithTimeout(context.Background(), 1*time.Second)
defer cancel()
// create database connection
psqlInfo := fmt.Sprintf("host=%s port=%s user=%s "+
"password=%s dbname=%s sslmode=disable",
c.Database.Host, c.Database.Port, c.Database.User, c.Database.Password, c.Database.DbName)
db, err := sql.Open("postgres", psqlInfo)
err = db.PingContext(ctx)
tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelSerializable})
// create temporary table to store ids
_, err = tx.ExecContext(ctx, "CREATE TEMPORARY TABLE temp_id_table (id int)")
// fetch all articles of set
newrows, err := db.QueryContext(ctx, "SELECT id FROM article WHERE setid = $1", SetId)
var tempid int
var ids []interface{}
for newrows.Next() {
err := newrows.Scan(&tempid)
ids = append(ids, tempid)
}
// adding found ids to temporary table so we can use it in other queries
var buffer bytes.Buffer
buffer.WriteString("INSERT INTO temp_id_table (id) VALUES ")
for i := 0; i < len(ids); i++ {
if i>0 {
buffer.WriteString(",")
}
buffer.WriteString("($")
buffer.WriteString(strconv.Itoa(i+1))
buffer.WriteString(")")
}
_, err = db.QueryContext(ctx, buffer.String(), ids...)
// fething article codes
currrows, err := db.QueryContext(ctx, "SELECT code FROM article_code WHERE id IN (SELECT id FROM temp_id_table)")
(I simplified the code and removed all error handling to make the code more readable)
When I change it to a normal table everything works fine. What do I do wrong?
EDIT 05-06-2019:
I created a simple test program to test new input from the comments below:
func main() {
var codes []interface{}
codes = append(codes, 111)
codes = append(codes, 222)
codes = append(codes, 333)
config := config.GetConfig();
// initialising variables
ctx, cancel := context.WithTimeout(context.Background(), 1*time.Second)
defer cancel()
// create database connection
log.Printf("create database connection")
db, err := connection.Create(config, ctx)
defer db.Close()
if err != nil {
log.Fatal(err)
}
// create transaction
log.Printf("create transaction")
tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelReadUncommitted})
if err != nil {
log.Fatal(err)
}
// create temporary table to store IB codes
log.Printf("create temporary table to store codes")
_, err = tx.ExecContext(ctx, "CREATE TEMPORARY TABLE tmp_codes (code int)")
if err != nil {
log.Fatal(err)
}
// adding found IB codes to temporary table so we can fetch the current articles
log.Printf("adding codes to temporary table so we can fetch the current articles")
_, err = tx.QueryContext(ctx, "INSERT INTO tmp_codes (code) VALUES ($1),($2),($3)", codes...)
if err != nil {
log.Fatal(err)
}
testcodes, err := tx.QueryContext(ctx, "SELECT * FROM tmp_codes")
if err != nil {
log.Fatal(err)
}
defer testcodes.Close()
var testcount int
for testcodes.Next() {
testcount++
}
log.Printf(fmt.Sprintf("%d items in temporary table before commit, %d ibcodes added", testcount, len(codes)))
// close transaction
log.Printf("commit transaction")
tx.Commit()
}
