How to specify table names in the command line and use them inside a .sql script

[kpym]

I am trying to find a way to specify table names in the command line and use these names inside a .sql script when executing a query with csvq. For example using environment variables in the FROM clause of my SQL queries. However, it seems that csvq does not accept environment variables directly in the FROM clause.

Example Scenario:

1. Command Line Execution:

   T=my_table.csv csvq -s my_script.sql

2. SQL Script (my_script.sql):

   -- Example script demonstrating the issue with environment variables in FROM clause
   SELECT * FROM @%T;

In this example, @%T represents the environment variable T that I want to dynamically substitute with a table name from the command line when executing csvq.

Is there a way to achieve this functionality in csvq? If not, are there any recommended workarounds or best practices to achieve similar dynamic behavior?

Any help or insights would be greatly appreciated. Thank you!

[mithrandie]

You cannot specify environment variables directly as table names, but you can assemble a string and execute it.

EXECUTE "SELECT * FROM %s" USING @%T;

cf. https://mithrandie.github.io/csvq/reference/built-in.html#execute

[kpym]

@mithrandie this is a good point, thanks. 🙏

Is there a specific reason why variables cannot be used as table names, or why we cannot specify tables in the command line? It seems like a pretty standard use case to have an SQL file that you want to apply to different CSV files.

[mithrandie]

Because it was a case of not much use for me...🙇‍♀️

For me, it is important to be able to join multiple tables, and I also thought that specifying any number of table names from the outside would be too complicated. However, it might be a good option to allow variables to be used as table names.

[kpym]

and I also thought that specifying any number of table names from the outside would be too complicated

A potential solution is to allow combining SQL expressions and sources as follows:

csvq --exp <expression1> --source <file1.sql> --exp <expression2> --source <file2.sql>

This would be treated as a single script, with the order of --exp and --source parameters respected:

expression1;
<contents of file1.sql>;
expression2;
<contents of file2.sql>;

With this approach, we can use variables as table names by defining them with --exp before --source.

Additional Notes

• The --source option can be compatible with [query] (the first positional argument), allowing multiple positional arguments to be treated as --exp parameters.
• This change maintains backward compatibility.
• Allowing variables as table names simplifies handling files with unusual names, such as those containing backticks (`).