Is it possible to use a query that has the table name dynamically detected?
In my example:
I have several annual tables (vw_mgana_cg122, vw_mgana_cg123, vw_mgana_cg124, etc…) In their name, the last two digits correspond to the year.
I should therefore query from table *123 if the session variable is the year 2023… otherwise I would read from *124 if I refer to the current year.
Thanks to those who will help me
No, that’s not possible. You can only use parameters for values, not for table names.
I know there are restrictions/limitations with PDO statements. However, creating variables and using them with a custom query seems to work. Eg:
SELECT {{dyn_col}} FROM {{dyn_table}} WHERE {{dyn_where}} ORDER BY {{dyn_orderby}} DESC
(although running queries from within the custom query panel doesn’t work).
Should this work? If so, should it be possible to run the query from within Wappler? I appreciate there are security issues with dynamic queries but I would have thought it’s possible to address these.
Another approach would be to create the query in a variable and use a stored procedure to run it.
Here is a workaround I’ve used (on client-side, using dmx-variable, a flow and a line of Javascript)…
If we use it carefully, this workaround can vbe usefull in a few cases
I have achieved Dynamic SQL Queries by creating an extension which runs PostgreSQL query with variables.
2 Likes