Combine a table-returning function with the source table

Mark Clements mark.clements at
Wed Jun 13 22:21:35 CEST 2018


This is a good question. For the specific example, you are quite correct.

However, the motivation for the question came from using the MonetDB-rmath extension and embedded R: often it is more efficient to calculate a number of values (e.g. the confidence intervals and p-values) together and then return all of the values as a table. This probably also reflects that I am a statistician and an old R user.

The recent article by Raasveldt suggested that analysis objects from Python could be stored natively rather than being serialised BLOBs. This could be further extended to storing R objects natively. Members of the objects could then be extracted as they are needed. Admittedly, this would lose many relational advantages. My suggestion is halfway, where R and Python can be used for analytics, with simple relational storage of the results.

Actually, is there any way to optimise a lateral call using BATs?

-- Mark

On 13 Jun 2018 17:43, Stefan Manegold <Stefan.Manegold at> wrote:

Hi Mark,

just out of curiosity,
(in the given case/example) what's wrong with a simple

select *, mean-1.96*sd as lci, mean+1.96*sd as uci from test;


create or replace function ci(se double)
returns double
 return 1.96*se;
select *, mean-ci(sd) as lci, mean+ci(sd) as uci from test;

in case you really want/need to avoid the "redundant" occurrence or "1.96"



----- On Jun 13, 2018, at 5:30 PM, Mark Clements mark.clements at wrote:

> Jennie,
> Thank you for replying to this inquiry - it's appreciated.
> Your solution gives the right result - but it requires changing the
> function call and will not generalise easily to other column types or
> other number of covariates.  If we had another table such as:
> drop table test;
> create table test as select 1 as id, 2 as sex, cast(3 as double) as
> mean, cast(4.0 as double) as sd;
> -- and repeating the function
> create or replace function cis(mu double, se double) returns table(lci
> double, uci double)
> begin return select mu-1.96*se, mu+1.96*se;
> end;
> we can get all of the test table combined with the confidence interval by:
> -- row_number()
> select * from
> (select *, row_number() over() as rid from test) as t1
> natural join
> (select *, row_number() over() as rid from cis((select mean,sd from
> test))) as t2;
> -- or using lateral
> select * from test, lateral cis(test.mean,;
> The row_number solution is fast, and the lateral solution is simple. I
> was hoping for:
> select *, cis(mean,test).* from test;
> Again, thank you for your help.
> -- Mark
> On 06/08/2018 01:53 PM, Ying Zhang wrote:
>> Hai Mark,
>> I’m just wonder if this gives you wat you want:
>> create function cis(mu double, se double)
>> returns table(mu_out double, lci double, uci double)
>> begin
>>   return select mu, mu-1.96*se, mu+1.96*se;
>> end;
>> Regards,
>> Jennie
> _______________________________________________
> users-list mailing list
> users-list at

| Stefan.Manegold at | DB Architectures   (DA) |
|<>  | Science Park 123 (L321) |
| +31 (0)20 592-4212     | 1098 XG Amsterdam  (NL) |
users-list mailing list
users-list at

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <>

More information about the users-list mailing list