Search code examples
postgresqlgosqlc

Optional filter on query with sqlc w/ Postgres in Golang

I'm writing a query where I want to fetch some asset transactions from my database, I'm using sqlc to generate queries for me, but I cannot figure out how to do this properly.

I want to give an optional asset ID (UUID). If the asset ID given is null (represented by a pgtype.UUID{Valid: false}) then I want to fetch all asset transactions irrespective of the asset, but when I provide a non-null value, I want asset transactions only for that asset ID.

This is my query at the moment:

SELECT * FROM asset_txn WHERE asset_id = $1;

ChatGPT suggested some things like:

SELECT * FROM asset_txn WHERE asset_id = COALESCE($1, asset_id)

The problem is that when sqlc generates the code for the query, the asset ID comes as a uuid.UUID, not a pgtype.UUID, meaning that I cannot give a null value. This is expected behaviour though, because my asset_id is a required field. Need help figuring out how to check if the asset_id is null and then proceed.

Solution

  • Got an answer from this comment:

    Just modified my query like the following:

    SELECT * FROM asset_txn 
WHERE (asset_id = $1 
    OR NOT @filter_by_asset_id::bool);

    This allowed me to add an asset_id if I wanted to filter by one, otherwise it selects all asset transactions.

    Another option is from here:

    SELECT * FROM asset_txn 
WHERE asset_id = COALESCE(sqlc.narg('asset_id'), asset_id);