Dynamic queries are difficult to model when starting from a static query since SQL doesn't compose well. The workarounds I've used:
- Use multiple queries that share the same output type.
- Push the predicate into the query directly.
However, you can't pass an expression for an order by column since the order by clause takes a name, not an expression. Postgres doesn't allow using names as arguments to a prepared query so that leaves either adding an annotation like sqlc.order_by that's dynamically added to a query string, or by getting more creative with the structure of the query:
SELECT * FROM AUTHORS WHERE sqlc.arg('by_date') ORDER BY DATE
UNION ALL
SELECT * FROM AUTHORS WHERE NOT sqlc.arg('by_date')
- Use multiple queries that share the same output type.
- Push the predicate into the query directly.
However, you can't pass an expression for an order by column since the order by clause takes a name, not an expression. Postgres doesn't allow using names as arguments to a prepared query so that leaves either adding an annotation like sqlc.order_by that's dynamically added to a query string, or by getting more creative with the structure of the query: