How to use a variable to fetch different table with pgx driver?

Question

Here is a function to fetch for french proverbs :

func (r *proverbRepo) SelectFrByDegree(search string) (proverbs []domain.Proverb, err error) {
    rows, err := r.Db.Query(context.Background(), `SELECT ID, proverb
        FROM proverbs_fr 
        WHERE proverb % $1
        ORDER BY proverb <-> $1 DESC LIMIT 5
    `, search)
    if err != nil {
        return
    }

    for rows.Next() {
        var prov domain.Proverb
        err = rows.Scan(&prov.ID, &prov.Literal)
        if err != nil {
            return
        }
        proverbs = append(proverbs, prov)
    }
    return
}

Sometimes I want to fetch another proverb table from per say proverbs_bzh table. Is it possible to fetch a different table with a variable name ?

SELECT * FROM $1

for instance...

Related / possible duplicate: [Golang ORDER BY issue with MySql](https://stackoverflow.com/questions/30867337/golang-order-by-issue-with-mysql/30867686?r=SearchResults#30867686) — icza, Oct 16 '19 at 07:03

score 0 · Accepted Answer · answered Oct 16 '19 at 06:31

That is not intention of bind variables. It should be used for data input on query reuse. If table is not known, query can not be parsed and execution plan reused. So, your option is to substitute string.

Warning: If table name comes from external source, be sure to check that it is proper identifier (hint: protect from SQL injection)

How to use a variable to fetch different table with pgx driver?

1 Answers1