Using Javascript to generate postgres query fragments

[Taka_Binancio]

I have a sql query where i want to change the predicate based on the value of a retool variable.
e.g.,
retool_var.value = True | False

In concept, I want to do something like:
SELECT * FROM mytable WHERE {{retool_var.value ? "mycolumn > 0" : "mycolumn < 123"}}

Apologies if this is simple as I'm new to Retool (and Javascript and logic and ....).

[joeBumbaca]

Hey @Taka_Binancio! The syntax and settings depend on what part of the query you are trying to make dynamic. Here's a couple of examples:

This can run as is, and will send either firstName = Joe or firstName = Ben

For a query a bit more similar to yours, we can make it work BUT you also have to disable prepared statements.

Let me know if you have any other questions!

[Taka_Binancio]

Thanks Joe. Yeah, it's the second case. Actually, I need to change the fields as well. something like:

select * from users where {{switch.value ? 'field1 < 10' : 'col1 < 10' : 'col2 > 30'}}

I assume that with prepared statements disabled, it's just a string. Will evaluate the security trade off.

[joeBumbaca]

Sounds good! We have seen customers create 2 resources to the same db

1. With prepared statements enabled for the vast majority of queries
2. With prepared statements disabled for one off queries such as this use case.

Hope that helps give you some paths to follow!