CODEWITHSUNDEEP
sqlpostgresqlgo
sravis
sravis

Reputation: 3680

How to make query parameters optional

In Postgres in Go, how can I make query parameters optional?

In this example status is an optional condition. If no status is passed all rows from table records will be fetched.

How to make query parameter &d.Status an optional

type QueryParams struct {
    Status string `json:"status"`
}

func (r repo) GetRecords(d *QueryParams) ([]*Records, error) {
    statusQuery := ""

    if d.Status != "" {
        statusQuery = " where status = $1 "
    }

    query := "select id, title, status from records " + statusQuery
    rows, err := r.db.Query(query, &d.Status)
}

Upvotes: 6

Views: 2829

Answers (2)

mu is too short
mu is too short

Reputation: 434606

Query is variadic so you could build an []interface{} to hold the arguments:

args := []interface{}{}

and then to conditionally build the argument list:

if d.Status != "" {
    statusQuery = " where status = $1 "
    args = append(args, &d.Status)
}

When you run the query, expand the arguments using ...:

rows, err := r.db.Query(query, args...)

Upvotes: 5

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520928

You may use a flexible WHERE clause, e.g.

SELECT id, title, status
FROM records
WHERE status = $1 OR $1 IS NULL;

The logic here is that if you provide a value for $1, it must match the status in order for a record to be returned. Otherwise, if $1 be left out (i.e. is NULL), then all records would be returned.

Note that to make this work from Go with the Postgres driver, you may need to do some extra massaging. I would try, at a first attempt, this:

statusQuery = "where status = $1 or $1::text is null"
query := "select id, title, status from records " + statusQuery
rows, err := r.db.Query(query, &d.Status)

Sometimes the driver can't figure out the type of the bound parameter $1. By explicitly casting it to text, the statement can be made to work.

Upvotes: 0

Related Questions