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