Hello Imad,

Yes, that’s the expected behaviour.  For such table-returning python functions, you can either pass all parameters as scalars, or as database columns.  There are several alternatives:

1. expand the scalar parameter as a datacolumn, e.g.:

SELECT * FROM f1( (SELECT col1, col2, col3, 42 FROM mytable) );

2. Store the value for scalar1 in SQL, and retrieve it in your Python UDF body using a loopback query [1], e.g.:

DELCARE scalar1 INT;
SET scalar1 = 42;

-- Note the changed function signature:
CREATE function f1(col1 string, col2 int, col3 double)
RETURNS TABLE(col1 string, col2  double, col3 double, col4 double)
LANGUAGE PYTHON {
  ...
  scalar1 = (_conn.execute(“SELECT scalar1;”).values())[0][0]
  ...
};

3. Use loopback queries in the Python UDF body to retrieve the columns, e.g.:

CREATE function f1(scalar1 int)
RETURNS TABLE(col1 string, col2  double, col3 double, col4 double)
LANGUAGE PYTHON {
  …
  col1 = list(_conn.execute("SELECT col1 FROM mytable;”)[‘col1’])
  col2 = list(_conn.execute("SELECT col2 FROM mytable;”)[‘col2'])
  col3 = list(_conn.execute("SELECT col3 FROM mytable;”)[‘col3’])
  ...
};

However, I’ve just noticed that different ways of accessing column data (i.e. option 1 vs option 3) have very different speed.  In my own little program, option 3 is ~40 times slower than option 1, which is rather unexpected.  You’re welcome to try different options, and please let us know if you notice any significant differences in execution times.

Cheers,

Jennie

[1] https://www.monetdb.org/blog/voter-classification-using-monetdbpython

> On 28 Aug 2017, at 11:52, imad hajj chahine <imad.hajj.chahine@gmail.com> wrote:
>
> Hi,
>
> I want to create a Python function that accept a query as argument, do some processing and return another table, In addition to the table columns I want to have some additional scalar parameters and i am getting the following error: function requires a single sub query.
>
> CREATE function f1(col1 string, col2 int, col3 double, scalar1 int)
> RETURNS TABLE(col1 string, col2  double, col3 double, col4 double)
> LANGUAGE PYTHON
>
> Is this the normal behavior of Table function, as in aggregate function I can combine columns with scalar parameters, any workaround for this issue.
>
> Thank you.

> _______________________________________________
> users-list mailing list
> users-list@monetdb.org
> https://www.monetdb.org/mailman/listinfo/users-list

_______________________________________________
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list