CODEWITHSUNDEEP
postgresqlgo
HackToHell
HackToHell

Reputation: 2393

How do I pass a param to db.exec

Consider the db.Exec statement,

db.Exec("INSERT INTO $1 values($2,$3,to_timestamp($4),var1,var2,var3,var4)

^^ works however if I want to do something like

db.Exec("INSERT INTO table_name_$1 values($2,$3,to_timestamp($4),var1,var2,var3,var4)

It doesn't work, $1 gets appended to the table name instead of the values of $1, how do I add it to the query ?

Upvotes: 2

Views: 3387

Answers (1)

Herman Schaaf
Herman Schaaf

Reputation: 48475

Since you say the first example works, one solution is to pass in the full table name as a parameter, doing the string concatenation beforehand:

db.Exec("INSERT INTO $1 values($2,$3)", "table_name_"+tbl, "this", "that")

You could also format the string manually with fmt.Sprintf and %s, as suggested in the comments.

Prepared statements

I am a little surprised that your first example works, as this is not valid syntax in Postgres. If you create a prepared statement with the table name as a parameter, it returns a syntax error:

# PREPARE fooplan (text, bool, numeric) AS
    INSERT INTO $1 VALUES($2, $3);
ERROR:  syntax error at or near "$1"
LINE 2:     INSERT INTO $1 VALUES($2, $3);

Whereas the second example you gave also appends $1 to the name of the table, so the library is behaving correctly:

# PREPARE fooplan (text, bool, numeric) AS
    INSERT INTO table_name_$1 VALUES($2, $3);
ERROR:  relation "table_name_$1" does not exist
LINE 2:     INSERT INTO table_name_$1 VALUES($2, $3);

So in your first example the library might be acting in your favor, but this is probably not something you should rely on too heavily.

The safest long-term solution is probably to use fmt.Sprintf, with the necessary escaping, to format the table name, and then use that in the Exec call:

sql := fmt.Sprintf(`INSERT INTO table_name_%s values($1,$2,to_timestamp($3))`, tbl)
db.Exec(sql, params...)

Upvotes: 3

Related Questions