Is it possible to use dynamic table name in "FROM"?

[vsbabu]

Recently discovered sqlpage and it took about a day to build a dashboard out of my bank statement. SQLPage is a lovely project for the ease to convert ideas to UI. Now facing a small challenge - my queries are all in one file executed as run_sql(file_name, parameters) displayed as a dynamic component. All SQL statements inside that have same CTEs at the top to set a date range, category list etc from query string. To reduce the size of SQL, created a temporary table and filled it up with filtered data.

I can drop the temporary table if it exists and proceed since I'm the only one using now. However, when I try to use a $ variable as table name, sqlpage is unable to parse the statement.

SET filtered = 'filtered_'||sqlpage.random_string(12);
select $filtered; -- this is fine; example output is  filtered_duR6Go7BD6fJ
drop table if exists $filtered;   -- all these fail to parse at $
create temporary table $filtered ...
select * from $filtered ...

PS: I don't like the idea of dynamic FROM clause. This is simply to reduce the code size (m4 macros is a good alternative:). An added advantage with temporary table is that I get a cache for some 6 queries further along.

Thanks

[lovasoa]

Hello, and welcome to SQLPage!
I think there may be a misunderstanding about how temporary tables or connection handling in SQLPage works... I wrote a short post on the subject recently: #871 (comment)

Using a fixed static name for a temporary table should just work, no matter how many requests you process concurrently.

[vsbabu]

Thanks! I had tested temporary tables on two side by side sessions using sqlite3 cli instances and that was fine. In sqlpage, when I quickly refresh, table still exists at times - this is because of connection reuse. I think your explanation has the key part - temporary table may exist but data will be different for different connections. Since one connection is used ONLY by one session at a time, I can safely drop table if exists and proceed.

Thank you!

[lovasoa]

In case someone else stumbles on this discussion later, and needs dynamic from clauses for other reasons.

If you’re looking to use a dynamic table name in FROM, be aware that SQLPage does not support this and probably never will, due to performance and security concerns.

If you have two tables, a and b, with identical columns, you should:

• Normalize your schema: Having multiple tables with the same structure often indicates a design issue. Consider restructuring your data.
• Use a view if normalization isn't an option:

  CREATE VIEW ab AS
  SELECT 'a' AS source_table, * FROM a
  UNION ALL
  SELECT 'b' AS source_table, * FROM b;

  This combines both tables while preserving their origin.