Combine a table-returning function with the source table

Mark Clements mark.clements at ki.se
Wed Jun 13 17:30:37 CEST 2018


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,test.sd);

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



More information about the users-list mailing list