0

Is possible to nest UDF's within each other ?

Following is a code for computing confidence intervals in A/B tests - Ofcourse, I could write a huge function that does all-in-one, but wondering a better way to achieve this goal ?

set search_path to public;
create function cumnormdist(x float)
  returns float
IMMUTABLE AS $$
  import math
  b1 = 0.319381530
  b2 = -0.356563782
  b3 = 1.781477937
  b4 = -1.821255978
  b5 = 1.330274429
  p = 0.2316419
  c = 0.39894228
  h=math.exp(-x * x / 2.0)
  if(x >= 0.0) :
    t = 1.0 / ( 1.0 + p * x )
    return (1.0 - c * h * t *( t *( t * ( t * ( t * b5 + b4 ) + b3 ) + b2 ) + b1 ))
  else :
    t = 1.0 / ( 1.0 - p * x );
    return ( c * h * t *( t *( t * ( t * ( t * b5 + b4 ) + b3 ) + b2 ) + b1 ))
$$ language plpythonu;


set search_path to public;
create or replace function conversion(experience_total float,experience_conversions float)
  returns float
IMMUTABLE AS $$
  return experience_conversions*1.0/experience_total
$$ language plpythonu;


create or replace function zscore(total_c float,conversions_c float,total_t float,conversions_t float )
  returns float
IMMUTABLE AS $$
  import math
  z = conversion(total_t,conversions_t )-conversion(total_c,conversions_c) # Difference in means
  s =(conversion(total_t,conversions_t)*(1-conversion(total_t,conversions_t)))/total_t+(conversion(total_c,conversions_c)*(1-conversion(total_c,conversions_c)))/total_c
  return float(z)/float(math.sqrt(s))
$$ language plpythonu;

create or replace function confidence(total_c float,conversions_c float,total_t float,conversions_t float )
  returns float
IMMUTABLE AS $$
  import math
  return **(1-float(cumnormdist(zscore(total_c float,conversions_c float,total_t float,conversions_t float )),4))*100.00**
$$ language plpythonu;

The individual calls work fine, eg : select cumnormdist (-3.1641397476); If I insert them in the function definition, they don't, for example zscore that calls conversion function.

ERROR:  NameError: global name 'zscore' is not defined. Please look at svl_udf_log for more information
DETAIL:  
  -----------------------------------------------
  error:  NameError: global name 'zscore' is not defined. Please look at svl_udf_log for more information
  code:      10000
  context:   UDF
  query:     0
  location:  udf_client.cpp:298
  process:   padbmaster [pid=3585]
  -----------------------------------------------

If I could nest functions inside each other,(instead of having UDF's as above that are finally nested) that would be a reasonable status-quo.

End goal : Publish these computations in Tableau.

1 Answer 1

3

Here's how I solved it. UDF's cannot cross-reference the contents of another UDF, so you can create a custom library, upload it to AWS using CREATE library.

More here

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.