Using dynamic view name from dropdown

💬 App Building
1

  • Goal: I have an app for showing metrics based on various parameters connecting to PostgreSQL database, I want to dynamically select the view I'd like to query via dropdown.

  • Steps: In the query I am using it like this:

select count(distinct(some_field))
FROM {{ viewSelector.value }} AS ad
join some_table t on t.id = ad.some_field
join some_tablev2 c on c.id = t.some_field
join some_table oe on c.some_field = oe.some_field
where
(COALESCE({{textInput1.value}}, '') = '' OR c.some_field = {{textInput1.value}})
AND (COALESCE({{numberInput1.value}}, 0) = 0 OR oe.some_field >= {{numberInput1.value}})
AND (COALESCE({{numberInput2.value}}, 0) = 0 OR EXTRACT(YEAR FROM t.date) = {{numberInput2.value}})


- Details: The problem is whenever I run the above query I am getting syntax error near ",". The query is working just fine, if I just copy it in different tool after changing the {{ viewSelector.value }}. I've used trim, replace and all other functions but had no luck. Also prepared statements are disabled.

Any idea what might be the issue here?
2

Untested - A hack that's worked for me in other platforms that might work here:

Build SQL as a string in a JS query block or variable
Reference and execute in your Postgre query block

1 Like
3

this worked, hacky way and needs some workaround because of the nature of my app but it works thank you :raised_hands:

4

Welcome to the community, @Oguz_Gul! For context, the initial error you shared is the result of prepared statements. You can disable this in the resource configuration, , but we don't necessarily recommend doing so for security reasons. Let us know if you have any additional questions!

5

I've disabled it but still got the error that was my confusion. As I've seen in other discussions, it was direct approach to disable it and just use the value but did not work for me.

6

Ah I just noticed that you mentioned having already disabled prepared statements. :+1: That's very odd, as it seems to be a pretty classic case. I tested it locally with a slightly simpler example and it executes as expected.

image
image966×462 38.7 KB

7

I'm glad you have a functional workaround, @Oguz_Gul, but am still interested in figuring out what's going on here if you have the time to take a closer look! Out of curiosity, were you running this query in a staging environment? We recently fixed a bug that caused prepared statements to only become disabled in the production environment.