Parameterize sql query in go

Question

I have a simple query like the one below.

select json_agg(row_to_json(t)) from (select *                                                       
               from jobs, companies, locations                                                                                                                                                                                                                                 
               where jobs.company_id = companies.id and jobs.location_id = locations.id                                                                                                                                                                                        
               $extra                                                                                                                                                                                                                                                          
               and to_tsvector(jobs.name || ' ' || companies.name || ' ' || locations.name) @@ to_tsquery($1)                                                                                                                                                                  
               and to_tsvector(locations.name) @@ to_tsquery($2)                                                                                                                                                                                                               
               limit $3) t

But it has some problems regarding additional query. The $1 and $2 parameters is optional based on the query user requests. But still, at least in this case to_tsquery (postgresql) doesn't support any "match all" query like '*' so we must have the whole additional query and to_tsvector(locations.name) @@ to_tsquery($2) with proper parameter or not at all.

This makes building query based on parameter tiresome work since we have to copy same query and add additional query all the time, and it's easily add up. I have a solution in mind to use strings.Replace to add up additional queries, but still we need to escape the parameter if needed. Is there any other solution can get this done in a clean way ?

So the short version is "can I do AND queries in PostgreSQL text-search query strings instead of the default OR search"? — Craig Ringer
– Craig Ringer, Commented Apr 14, 2015 at 7:04

Moch Lutfi · Accepted Answer · 2015-04-28 07:28:21Z

4

Here the tricky solution, hope this can help you. this query example using syntax query for github.com/lib/pq

baseQuery := `
        SELECT *
        FROM
            tableName
        WHERE
            (name ILIKE $1)`

params := []interface{}{"%" + nameLike + "%"}

// you can add optional criteria using this way
if condition==true {
        baseQuery += fmt.Sprintf(` AND student_no=$%d`, len(params)+1)
        params = append(params, studentNo)
}

filterQuery := baseQuery + ` ORDER BY contract_no ASC`

if totalLimit != 0 {
    filterQuery += fmt.Sprintf(` LIMIT $%d OFFSET $%d`, len(params)+1, len(params)+2)
    params = append(params, totalLimit, totalSkip)
}

rows, err := db.Query(filterQuery, params...)

answered Apr 28, 2015 at 7:28

Moch Lutfi

55210 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tzu ng Over a year ago

thanks for pointing out. I have implemented with the similar idea :)

Tzu ng Over a year ago

yes will do. But I will leave it for now, just in case someone have better idea :)

Collectives™ on Stack Overflow

Parameterize sql query in go

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related