Pass varchar parameter to R UDx function

VaughanR0

I am having trouble sorting out how to pass a varchar parameter to an R UDx function.  Here are some details.

predictFactory <- function() {
	list(name=vpredict,
		udxtype=c("transform"),
		# intype=c("varchar","float"),
		intype=c("any"),
		parametertypecallback=predictParams,
		outtype=c("any"),
		outtypecallback=predictReturnType)
}

predictParams <- function() {
	param = data.frame(datatype=rep(NA,4),length=rep(NA,4),scale=rep(NA,4),name=rep(NA,4))
	param[1,1] = "int"
	param[1,4] = "fmths"
	param[2,1] = "int"
	param[2,4] = "etsOK"
	param[3,1] = "varchar"
	param[3,2] = 12
	param[3,4] = "calcDate"
	param[4,1] = "int"
	param[4,4] = "test"
	param
}

I call the 'R' function using

select predict(MonthDate, Qty using parameters fmths=12, etsOK=0, calcDate='1/3/2015') over(partition auto) into table fcast_data from hist_data;

Inside the 'R' function I try to recover the parameters using:

	# Handle parameters in the list
	if(exists('fmths', where=y)) {
		fMths = as.numeric(y[['fmths']])
	} else {
		fMths = 12		# default
	}
	if(exists('etsOK', where=y)) {
		etsOK = as.logical(y[['etsOK']])
	} else {
		etsOK = TRUE		# default
	}
	if(exists('calcDate', where=y)) {
		calcDate = dmy(y[['calcDate']])	# in d/m/y or d-m-y format
	} else {
		calcDate = today()		# default
	}
	if(exists('test', where=y)) {
		testmode = as.logical(y[['test']])
	} else {
		testmode = FALSE		# default
	}

and I am using lubridate package to handle dates within 'R' so I can be flexible with the format of the date string passed in.

 

The trouble is calcDate inside 'R' never gets assigned the parameter value and so always gets set to the current date.

 

I have tried casting to a varchar using cast('1/3/2015' as varchar) in the parameter list but this does not work either.

 

Is anyone able to help me sort out what I am doing wrong?

 

I know I could pass the date as an int and convert that back to a date inside 'R', but would rather like to work out why using varchar does not work.

VaughanR0

Well this is a surprise.  It appears that only lowercase characters are allowed in the parameter names.

When I change the code to:

predictParams <- function() {
    param = data.frame(datatype=rep(NA,4),length=rep(NA,4),scale=rep(NA,4),name=rep(NA,4))
    param[1,1] = "int"
    param[1,4] = "fmths"
    param[2,1] = "int"
    param[2,4] = "etsok"
    param[3,1] = "varchar"
    param[3,2] = 12
    param[3,4] = "calcdate"
    param[4,1] = "int"
    param[4,4] = "test"
    param
}

and

    if(exists('fmths', where=y)) {
        fMths = as.numeric(y[['fmths']])
    } else {
        fMths = 12      # default
    }
    if (debug == 2) vertica_log(toString(c("vpredict: parameter etsok: ", y[['etsok']])))
    if(exists('etsok', where=y)) {
        etsok = as.logical(y[['etsok']])
    } else {
        etsok = TRUE        # default
    }
    if (debug == 2) vertica_log(toString(c("vpredict: parameter calcdate: ", y[['calcdate']])))
    if(exists('calcdate', where=y)) {
        calcdate = dmy(y[['calcdate']]) # in d/m/y or d-m-y format
    } else {
        calcdate = today()      # default
    }
    if(exists('test', where=y)) {
        testmode = as.logical(y[['test']])
    } else {
        testmode = FALSE        # default
    }

and, of course, change the calling function to:

select predict(MonthDate, Qty using parameters fmths=12,etsok=1,calcdate='1/3/2015', test=0)
over(partition auto)
into table fcastData
from histData

It now all works.

 

HP what are you doing?? and why is it not documented!!