LUA Scrip -How to Emit data from table by filtering based on a parameter

Padawan

How can I have a Lua script that will allow to receive a parameter, execute a query based on the parameter and emits the resulting table 

 

CREATE OR REPLACE SCRIPT my_scrip_1(time_max ) RETURNS TABLE AS
exit(query([[SELECT col_1, col_2, created_at  FROM table1 WHERE created_at >= :c]], {c=time_max}))
/

 

 

Calling with:

EXECUTE SCRIPT my_scrip_1 ('2010-08-01 00:00:00');

does the job, returning all rows in table1 where created_at >= the given timestamp. 

However, I'd like to call the script not with the EXECUTE but rather as a select statement

e.g  SELECT my_scrip_1 ('2010-08-01 00:00:00');

is that possible?

Thanks in advance.

 

2 REPLIES 2

Team Exasol
Team Exasol

Let me explain a little bit why Charlie's answer is correct.

To interact with the database there is exactly one interface available: SQL. Whether the SQL is directed to the database via JDBC or ODBC is irrelevant. In any case it must be SQL.

If you use a SCRIPT (as above), this script will send a SQL query to the database via JDBC. UDFs do not have this interface. UDFs can take parameters or sets of parameters and return values or sets of values themselves, but they cannot access the database directly.

To check if the filter you set is effective or not, you would have to include the parameter as a WHERE predicate in the SQL string, which does not work for the reasons mentioned above.

For the moment you can do the following:
You can use a SCALAR EMITS UDF that processes a table row by row and applies the filter and returns rows accordingly if the filter applies. Since this can be slow I would recommend to execute all parts of the query that have nothing to do with the filter beforehand. Then write the results into a temporary table and apply the filter to this subset. The UDF can then write the results to a destination table. Finally, you must drop the temporary table again (or alternatively, before this process, disable autocommit and do not commit until you write to in your destination).

Xpert

Sadly this is not possible.

However there is an IDEA for that and you can upvote it (IDEA-42 )