Select from a different table depending on string interpolation

Queries and Resources
1

I have Postgres database with multiple tables, these tables are called connection_xxxxx, where those x's are replaced with the id of the connection. These tables are generated programatically, so there are thousands of them (as you can imply by the 5 digits!).

In Retool, I'd like to be able to query a specific table depending on the value that's written in an input, let's call it for simplicity: text1.

I've tried to write my SQL query in this way:

SELECT
  *
FROM
  "connection_{{ text1.value }}"
LIMIT
  10;

However, this results in the following error:

relation "connection_$1" does not exist

I know that the string interpolation works in queries when it's in the WHERE clause, for example, but it doesn't seem to work when I use it in the table name.

Is this a known limitation? If so, are there any workarounds I could use?

Otherwise, am I doing it incorrectly? What would be the correct way to do it?

Thanks! :pray:

2

This is explained here: Prepared statements | Retool Docs

Since most databases do not support dynamic column or table names in prepared statements, you generally cannot use {{ }} embedded expressions to dynamically specify columns or tables.

However, the workarounds there don't seem acceptable for my use-case, so I'm open to new ideas if possible!

3

I think one of the common workaround is to prepare the statement in a JS Query and then use SQL to EXEC the statement returned. Of course, this will allow users to inject SQL if the text1.value allows it or you do not sanitize the input before using it.

1 Like