How to make query parameters optional

Question

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)
}

mu is too short · Accepted Answer · 2020-01-03 17:54:37Z

5

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...)

edited Jan 3, 2020 at 17:54

answered Jan 2, 2020 at 4:07

mu is too short

436k71 gold badges863 silver badges822 bronze badges

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

1 Comment

Michael S. Over a year ago

This worked for me! (Though in my case I appended the actual value, d.Status, rather than its address, &d.Status). Thank you!!

Tim Biegeleisen · Accepted Answer · 2020-01-02 04:00:09Z

0

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.

answered Jan 2, 2020 at 4:00

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

4 Comments

Adrian Over a year ago

It's valid, it's just irrelevant to the question actually being asked. They've already got their dynamic SQL query working, they just need to know how to pass in the values for the parameters when executing the query.

Tim Biegeleisen Over a year ago

If no status is passed all rows from table records will be fetched. ... my answer seems to meet this requirement. No, the OP doesn't already have the query working, or else they would not have asked this question.

Adrian Over a year ago

Their code already meets that requirement if you actually read it. The entire WHERE clause is programmatically added to the query when needed. This answer is irrelevant to the question.

Michael S. Over a year ago

Maybe I'm missing something, but I had the same question as the OP and I found this answer helpful. It is often useful to have different strategies to choose from.

Collectives™ on Stack Overflow

How to make query parameters optional

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related