UDF's in redshift : Possible to reference a udf within another
Question: 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.


Answer: 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](http://docs.aws.amazon.com/redshift/latest/dg/udf-python-language-
support.html)

