VIEW based on UDF result

witold_zawieja
Contributor

Hi Exasol Community, I need your support.

I have to do some transformation for each row in the table. Due to complexity of this transformation I decided to create UDF.

Assumptions:

  • I want to read all rows from table inside the UDF and execute transformation in the loop for each row.
  • I can not create any table to store result of my transformations.

I have following questions:

  1. how can I read rows from SELECT statement inside UDF? I try to use "res=query([[SELECT ..." but I receive following error during execution:
    Lua Error "attempt to call global 'query' (a nil value)" caught in script
  2. Should my UDF return TABLE or EMITS? it is not clear for me what I should use.
  3. my UDF has no input parameter. Can I execute it in SELECT statement? e.g: SELECT my_fun() FROM DUAL;
  4. How can I create VIEW object based on result of UDF?

I'm hoping you can help me.

 

 

2 REPLIES 2

mwellbro
Xpert

Hi witold_zawieja,

I´ll address your question in reverse order:

4. => You may very well use CREATE VIEW MY_VIEW as SELECT my_func() from DUAL
3. => As seen in 4.), you can call your UDF like this in the select list part of a query

As for points 2 & 1 you seem to be mixing the concepts of UDF´s and SCRIPTS - the RETURNS TABLE syntax ( as well as RETURNS ROWCOUNT ) belong into the realm of SCRIPTs which are called with the EXECUTE SCRIPT command , are executed
in lua and allow you , for example , to address the "query" function you tried to call in your UDF ( where it´s not possible to do that ).
I´ve always thought of SCRIPTS as "containers for logic that´s more on the organisational side of things", stuff like creating tables, copying data from a to b in EXA or generating the DDL of your DB ( while you can also orchestrate entire ETL workloads and do much more with interesting stuff with SCRIPTs ).
SCRIPTs can´t be called in statements like SELECTs, UPDATEs, etc. , they are exclusively used via EXECUTE SCRIPT and the return value ( even if RETURNS TABLE ) is not accessible for further "SQL-based-processing".

By comparision, a UDF does things more along the lines of manipulating data ( at large volumes if need be ) with anything you program into it and return that data back through SQL - here is where the EMITs syntax comes into play, which alows you , once you´ve done to your data what needed to be done, to "generate one or more rows".

Accessing the DB from within an UDF is not as straight forward as in SCRIPTs, UDFs normaly "think" more along the lines of "passing the necessary data from SQL to UDF and let the UDF work its magic".

Maybe if you can outline ( in broad strokes ) what it is that the UDF / the view should do someone here might give you a couple of more ideas ?
It might even be that you can implement the requirement in "plain sql", even though it doesn´t lend itself as easily to tasks like what I imagine you are planing with the "some transformation for each row" 🙂

To get more of a feeling how UDFs "do their thing", maybe take a look at https://docs.exasol.com/database_concepts/udf_scripts/udf_examples.htm if you haven´t already.
Also, more to drive home the point regarding "access data from the DB" and pertaining to SCRIPTs ( not UDFs) :
https://docs.exasol.com/database_concepts/scripting/db_interaction.htm .

Hope this helps you with your question.

Cheers,
Malte

witold_zawieja
Contributor

thank you @mwellbro . You are right. I was mixing two functionalities. I did some practices and now it is clear for me. Thank you for explanations.