Combine a table-returning function with the source table

Ying Zhang Y.Zhang at cwi.nl
Fri Jun 8 13:52:01 CEST 2018


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

> On 30 May 2018, at 16:36, Mark Clements <mark.clements at ki.se> wrote:
> 
> As a follow-up to my earlier inquiry: how difficult would it be to allow
> for a table-returning function in the select clause in MonetDB? This
> elegant solution is possible in Postgresql:
> 
> test=> drop function cis(double precision,double precision);
> DROP FUNCTION
> test=> create function cis(mu double precision, se double precision)
> returns table(lci double precision, uci double precision) as 'select
> mu-1.96*se, mu+1.96*se' language sql;
> CREATE FUNCTION
> test=> select generate_series, (cis(generate_series,3)).* from
> generate_series(1,3);
> generate_series |  lci  | uci 
> -----------------+-------+------
>               1 | -4.88 | 6.88
>               2 | -3.88 | 7.88
>               3 | -2.88 | 8.88
> (3 rows)
> 
> Other wishes would include more complete support for window functions.
> The mal code by Moritz Bruder looked like a good start
> (https://www.monetdb.org/pipermail/users-list/2017-February/009709.html).
> 
> Kindly, Mark.
> 
> On 05/25/2018 11:06 AM, Mark Clements wrote:
>> For using the MonetDB-rmath extension, I was interested in using
>> table-returning functions to return multiple values. Is there a fast and
>> elegant way to combine the results with the source table in SQL? To make
>> this concrete, consider some test data and a function to calculate a 95%
>> confidence interval from a mean and standard error:
>> 
>> drop table test;
>> create table test as select cast(value as double) as value from
>> generate_series(0,5);
>> drop function cis(double,double);
>> create 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 then call the table-returning function:
>> 
>> select * from cis((select value, 1.0 from test));
>> 
>> If we want to re-join the results with the source table, we could use
>> row_number() or use lateral:
>> 
>> -- Using 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 value,1.0 from
>> test))) as t2;
>> 
>> -- Using lateral:
>> select * from test, lateral cis(test.value, cast(1.0 as double)) as t2;
>> 
>> I believe that the latter will lead to a slow loop, even when BAT
>> functions are available. The table-returning function can be used in the
>> select statement only for a scalar expression:
>> 
>> select cis(0,1).*; -- ok
>> select cis(0,1).* from test; -- Error: no such operator 'cis'
>> select cis(value,1).* from test; -- Error: identifier 'value' unknown
>> 
>> Again, is there a fast and elegant way to combine the source table with
>> a table-returning function in SQL?
>> 
>> Kindly, Mark.
>> 
>> _______________________________________________
>> users-list mailing list
>> users-list at monetdb.org
>> https://www.monetdb.org/mailman/listinfo/users-list
>> 
> 
> _______________________________________________
> users-list mailing list
> users-list at monetdb.org
> https://www.monetdb.org/mailman/listinfo/users-list



More information about the users-list mailing list