24-04-2020 12:30 PM
Hi,
while creating a UDF it seems an embedded SQL query cannot deal with function parameters, for example in the where-clause of the query.
The following code snipped is an example of the problem:
CREATE OR REPLACE FUNCTION MY_FUNC(myValue INTEGER)
RETURN INTEGER
IS
cnt INTEGER;
BEGIN
cnt := select count(*) from MY_TABLE where myColumn=myValue;
RETURN cnt;
END
/
When I call the function, e.g.:
select my_func(42);
I get the following error:
[0A000] Feature not supported: function parameters may not be accessed within scalar subqueries (Session: 1664854486638338049)
Does anyone know how to get around this?
Best regards
22-05-2020 09:56 AM
Depending on the context (which I don't know) this might help in your case.
create or replace connection exa_connection
to 'ws://<ip>:8563'
user 'SYS'
identified by 'exasol';
create or replace python scalar script MY_FUNC(myValue INTEGER)
RETURNS INTEGER
AS
import EXASOL
def run(ctx):
c = exa.get_connection('EXA_CONNECTION')
with EXASOL.connect(c.address, c.user, c.password) as connection:
with connection.cursor() as cursor:
cursor.execute("select count(*) from test.tmp where id_3 = ?", ctx.myValue)
ret = cursor.fetchone()
return ret[0]
/
;
select MY_FUNC(2);
Hope that helps
07-06-2020 09:08 PM
Hi @exa-AndreasS ,
I encountered the same problem while creating UDF. I tried swapping function parameter with created within UDF variable but I got the same error.
Is it possible to somehow access the schema with a SELECT inside the UDF(to use information stored in the tables) and use defined variables in the SELECT statement ? Is it maybe possible to do that with LUA script ?
// LIKE: z := (SELECT x FROM y where x = variable_defined_in_UDF) //
I am asking that 'cos using UDF with python works really slow and I was hoping to use plain procedural SQL to do that and my hope was it would be as fast as builtin functions.
Thanks,
Piotr
08-06-2020 12:12 PM
Hi Piotr,
to not mess things up, you maybe should open another discussion with your exact problem description.
Just to mention two things aside,
- procedural SQL is usually not the best approach to code in a columnar database (key thing here: storage organization within database)
- depending on what you want to achieve it might be a better use case for a "script" in Exasol: https://docs.exasol.com/database_concepts/scripting.htm
But again,
if this is not suitable solving your issue, please open a new conversation with more details on your specific case.
How your system looks like (DDL, sample data, ...) and what you try to achieve.
Best,
Andy
Then it's time to become part of a unique family! Discover helpful tips and support other Community members with your knowledge.
Sign In